List:General Discussion« Previous MessageNext Message »
From:hsv Date:October 11 2012 8:39pm
Subject:Re: column aliases in query
View as plain text  
>>>> 2012/10/11 13:46 -0400, Mark Haney >>>>
I know it's been a while since I wrote serious queries, but I'm sure I have done something
like this before:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id FROM
events JOIN machine ON events.mach_id = machine.mach_id WHERE machine.factory_id = "1"
AND vDate = "2012-10-11"

Where I've aliased the SUBSTR of the date and then used the alias in the WHERE clause of
the query.  I'm getting an error message now, but I'm almost certain I've used that
syntax before.  Am I missing something?
<<<<<<<<
Yes: WHERE is for already defined names. In HAVING one refers to new names:

SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id FROM
events JOIN machine ON events.mach_id = machine.mach_id WHERE machine.factory_id = "1"
HAVING vDate = "2012-10-11"

Thread
column aliases in queryMark Haney11 Oct
  • RE: column aliases in queryRick James11 Oct
  • Re: column aliases in queryhsv11 Oct