[Solved] Using Alias filed within a query

Creating tables and queries
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Using Alias filed within a query

Post by Maax555 »

Hi. is it not possible to use the result of an alias in the same query? I have an calculation using alias GM for example in a query. In the same query I then want to use the GM to create another alias?
Last edited by Maax555 on Tue Oct 23, 2018 10:39 am, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Using Alias filed within a query

Post by eremmel »

I do not follow you. Normally you can use table-aliasses at any place you can use a table-name; field(expression)-aliases can only be used in order by or in query that contains a derived table in which an field(expression)-alias is used.
Please post the your query for better understanding.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Using Alias filed within a query

Post by Maax555 »

Hi thanks, i found a different way to achieve my goal now. However i was trying to use the the resulting alias field, lets say GM in the same query. So i had two fields from queries to generate the alias GM then i wanted to use the GM result in same query to make a new alias, lets say GM x 1.33 = new alias GMResult. I was getting an error saying GM did not exist.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Using Alias filed within a query

Post by UnklDonald418 »

HSQL parses a SELECT statement all at once so an alias can't be used for a calculation in the same statement where it was defined.

Code: Select all

SELECT X AS GM, GM * 1.33 AS "GMResult" FROM "SomeTable" 

generates the Column not found exception.
You would need to use the original field name for the calculation in the SELECT statement. You can use the newly defined alias in a WHERE statement because it is parsed after the SELECT statement.

Code: Select all

SELECT X AS GM, X * 1.33 AS "GMResult" FROM "SomeTable"  WHERE GM > 1
I can't imagine why you would require using an alias but I suppose you could use something silly, like

Code: Select all

SELECT GM, GM * 1.33 AS "GMResult" FROM "SomeTable", (SELECT X AS GM FROM "SomeTable")
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Using Alias filed within a query

Post by Maax555 »

many thanks for help on this one. I ended up using another query and an alias.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply