I saw you had an aggregate function within your example but you said nothing
about using a TABLE ALIAS inside an aggregate function. Your question asked
if you could use an ALIAS in a WHERE clause.
You can use an ALIAS in a WHERE clause but Ive never been able to use an
ALIAS inside an aggregate function before. In that case the answer is no,
you cant do it & you might have to like you said code out 2 seperate
queries.
>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 23:15:29 -0700
>
>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
