The original question was field aliases, not necessarily table aliases
I just opened up my SQL Server, and ran this on the Northwind sample
database, and the message I get is 'Invalid Column Name', because the alias
fullname isn't a table column
SELECT firstname + ' ' + lastname AS fullname
FROM Employees
WHERE fullname = 'Margaret Peacock'
Same thing with this field alias maxID
SELECT MAX(employeeID) AS maxid
FROM employees
WHERE maxid > 4
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: Saturday, December 16, 2000 2:11 AM
> To: CF-Talk
> Subject: RE: SQL: Aliases in where clauses.
>
>
> 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
