0

Working on LibreOffice 4.4.5.2 / HSQLDB 1.8.0.10

Calculated fields are quite easy.. eg; "Field Name1" + "Field Name2" in a third field = a simple sum.

In my database I think I need further calculations from calculated fields within the same query.

These two sql statements are in the same query:

  • BuyPrice
  • S/H Paid
  • TaxPaid

"BuyPrice" + "S/H Paid" + "TaxPaid"

When the query runs, this outputs to a field with an alias of Total Cost

  • SellPrice
  • S/H Charged

"SellPrice" + "S/H Charged" - ( "SellPrice" * 0.132 + "S/H Charged" * 0.132 )

This outputs to a field with an alias of NET

This is exactly what I need, however I also need a third calculated field for Profit. I cant just enter "NET" - "Total Cost". If I create another query on top of the first one, I can reference the aliases and it works just fine but I can only get this into two separate "Table Controls"

Should this possibly be separate queries??

I simply don't know enough about any of this to get it to work.. any help or suggestions would be greatly appreciated

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
bdragon254
  • 13
  • 3

1 Answers1

0

As you have found, you cannot use aliases within queries. You have to write out the entire calculation

"SellPrice" + "S/H Charged" - ( "SellPrice" * 0.132 + "S/H Charged" * 0.132 ) - ("BuyPrice" + "S/H Paid" + "TaxPaid")

to alias it to a column PROFIT. If you write two queries, when the program runs it internally writes out all the code like this. Two queries adds a bit of overhead calculation for the queries to be combined. The overhead may be worth it if it makes the queries more maintainable by you.

Lyrl
  • 925
  • 6
  • 16
  • Thanks for the reply! Perfect! It worked like a charm. For you i'm sure this was simple, but for me I was lost. lol I immediately assumed the only way to do it was to calculate based off of the results from the other calculated fields. – bdragon254 Aug 18 '15 at 08:40