[Solved] Using Alias filed within a query

Creating tables and queries

[Solved] Using Alias filed within a query

Postby Maax555 » Fri Oct 05, 2018 1:47 pm

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Using Alias filed within a query

Postby eremmel » Fri Oct 05, 2018 5:16 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Using Alias filed within a query

Postby Maax555 » Tue Oct 09, 2018 9:07 am

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Using Alias filed within a query

Postby UnklDonald418 » Tue Oct 09, 2018 7:51 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1201
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Using Alias filed within a query

Postby Maax555 » Tue Oct 23, 2018 10:38 am

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests