COuld you give an example... I've been using RDMS's for over 6 years, and
haven't found one yet that'll let you use an alias (for an aggregrate
function) in a WHERE cause yet. It's possible to use them in a HAVING
clause, but not a WHERE clause...
Joseph E. Sheble
a.k.a. Wizaerd
Wizaerd's Realm
Canvas, 3D, Graphics,
ColdFusion, PHP, and mySQL
http://www.wizaerd.com
=================================
> -----Original Message-----
> From: Michael Thomas [mailto:[EMAIL PROTECTED]]
> Sent: Friday, December 15, 2000 6:30 PM
> To: CF-Talk
> Subject: Re: SQL: Aliases in where clauses.
>
>
> They've worked for me numerous times.
>
>
> >From: Joe Sheble aka Wizaerd <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: Re: SQL: Aliases in where clauses.
> >Date: Fri, 15 Dec 2000 13:19:54 -0700
> >
> >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
