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
