Apache OpenOffice Community Forum

Hi
I have some trouble with time calculation so i did it this way:
( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) - ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) )
I have a table with StartTid, SlutTid and it's only time value.
This gives a plan number i minutes and need som futher calculating.
If I did it this way:
Select ( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) AS "TS1", ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) ) AS "TS2", "TS1" - "TS2" AS "TS3" from table (table name)
The "TS1" - "TS2" AS "TS3", dos'nt work that could be if the TS1 and TS2 is string, then i need to cconvert it to numbers but how val("ts1") and Convert(some syntax) dosn't work.
To move forward on my conversion I also need to use if... then... else. or when... do... else.. end.
Any syntax on that?
Best regards

Rikard Svenningsen

Hello Rikard,

Well, you are correct the database engine used in the embedded ODB file does not allow you to use column aliases in other parts of the same query.

You can get around this - but it is one extra step.

Create two Base query definitions or create a single query using a sub select.

First example. Using the query designer create query1
Using the query designer create query2 - this time select query1 in the Add Table window, so the query will end up being
Functionally this is same as if you had written it as a sub-select

Thanks Drew, this is great. It works!
Problem solved