Issue 67726

Summary: calculated fields outcomes should be usable inside the same query
Product: Base Reporter: fornarif <fornarif>
Component: codeAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 2.0.3Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description fornarif 2006-07-24 16:41:26 UTC
this is not a bug report, but a request for a useful feature that Base doesn't
support yet.

Let's say I need to calculate a price markup based on some percentage rate. I
can build a query with two starting fields: "NetPrice" and "MkUpRate". Then, I
can create a third field inserting a simple formula:

"NetPrice" * "MkUpRate" / 100 AS "MarkUp"

Usually, users need also the sum of "NetPrice" and "MarkUp", and here there are
two ways to do it at present, with Base:

1) creating a fourth field with the following formula:

"NetPrice" + ("NetPrice" * "MkUpRate" / 100) AS "MarkUp" AS "Total"

but it's not a good (and safe) solution to repeat the same formula more than
once, mainly when it is more complicated.

2) creating a II level query (query in query) where it'll be possible to execute
the sum "NetPrice" + "MarkUp", avoiding repetitions of any formulas. But, this
way, we'd need two queries rather than one.

IMO, the best solution seems to be that one Access offers to its users: when
using calculated fields inside queries, users must declare them as "Expression",
by the same item available in the List Box on "Function" row. This way, Access
can recognize calculated fields and properly use their results.

I think this feature could help Base users to simplify a lot their projects,
avoiding many repetitions of their formulas.

Best regards and thanks in advance,
Comment 1 Rainer Bielefeld 2006-07-24 19:02:04 UTC
Changed Issue type and summary
Comment 2 Frank Schönheit 2006-07-27 12:40:23 UTC
hmm, I know I asked you to submit this issue, but I just found that it might be
a duplicate of the already-existing issue 66837 - what'd you say?
Comment 3 fornarif 2006-07-27 13:28:46 UTC

I think you'd be right, even if I can agree with the first description of the
issue 66837 only, and less with all additional comments, pointing out fairly
different problems, IMO.

Best regards,
Comment 4 drewjensen.inbox 2006-08-01 00:12:25 UTC
If I may put my $.02 in. I think the two issues are about the same thing. In a
nut shell, the ability to create a column alias in a select statemnt and use
that alias as a valid expression within another selectexpression in the same
select statement.

I would also argue that this is not an issue for OOo but rather for HSQLDB. It
is true that HSQLDB does not allow this, nor does MySQL for that matter. As you
point out MS Access does, and in fact I tested this - if you have a Base file
that connects to an MDB file you can do this from within Base now. You can not
do so within the designer, but turn the designer off and the parser will accept
it and the results will be as expected.

Drew Jensen
Comment 5 christoph.lukasiak 2006-08-16 16:16:04 UTC
send further to the requirement team - if this issue get needless (some other
enhancement cover it etc.), please close it

Comment 6 lymwaydurchting 2010-11-11 01:04:09 UTC
Created attachment 73961