Unfortunately, aliases are not available in a WHERE clause.. I've asked
this before in various different places, and the response has always been
the same.
At 01:17 PM 12/15/00 -0500, you wrote:
>Hi all.
>
>I was wondering, is it possible to use aliases in an sql where clause?
>
>We recently reorganized a bunch of data into new tables, and I am having to
>go back and update the templates that get data from the old tables, so that
>they get data from the new tables.
>
>Example:
>
><cfquery name=qcfdefaultSearch datASource="FSLibrary">
>SELECT events.EventID AS EVENT_ID_NUMBER, events.EventTitle AS
>EVENT_NAME,
>MIN(dates.EventDate) AS EventBeginDate,MAX(dates.EventDate) AS EventEndDate,
>contacts.ContactPhone_AC AS mainContactAreaCode, contacts.ContactPhone AS
>mainContactPhone, contacts.ContactEmail AS mainContactEmail,
>contacts.ContactFax_AC AS mainContactFaxAreaCode, contacts.ContactFax AS
>mainContactFax, venues.VenueName AS EventVenueName, venues.VenueCity AS
>EventVenueCity, venues.VenueState AS EventVenueState
>FROM dbo.tbl_EP_Events AS events, dbo.tbl_EP_EventDateTime AS
>dates,
>dbo.tbl_EP_Venues AS venues, dbo.tbl_EP_EventContacts AS contacts
>WHERE (EventBeginDate #dateCompare# #searchDate# and
>events.EventScopeID =
>#eventScopeVariable#)
>and contacts.ContactID = events.EventMainContactID
>and venues.EventVenueID = events.EventVenueID
>and dates.EventID = events.EventID
>ORDER BY EventBeginDate #SortOrder#
></cfquery>
>
>the problem is that the template chokes when I try to use WHERE
>(EventBeginDate #dateCompare# #searchDate# and events.EventScopeID =
>#eventScopeVariable#) it seems that it doesn't like the alias in the where
>clause. I tried using MIN(dates.EventDate), but that doesn't work either.
>
>Is it possible to use aliases in the WHERE statement, or am I going to have
>to do this as two separate tables?
>
>Thanks,
>
>Chris Martin
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
