Apache OpenOffice (AOO) Bugzilla – Issue 67726
calculated fields outcomes should be usable inside the same query
Last modified: 2013-08-07 15:45:17 UTC
Hello, 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, Franco
Changed Issue type and summary
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?
Hello, 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, Franco
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. Sincerely Drew Jensen
send further to the requirement team - if this issue get needless (some other enhancement cover it etc.), please close it thx
Created attachment 73961