Re: SQL: Aliases in where clauses.
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: > > >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 >FROMdbo.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# > > >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
Re: SQL: Aliases in where clauses.
That is some select statement. I've used aliases (for both tables and fields) in the where clause in SQL Server 7, and 2000. You didn't mention what database you were using, although I don't think that should make a difference. Chris Martin wrote: > > Hi all. > > I was wondering, is it possible to use aliases in an sql where clause? > > Example: > > > 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#) The previous condition doesn't look quite right. The datecompare variable contains some of comparison operator, such as '=' or '>='? and searchDate contains an ODBC compliant date? I would double check the values of those variables before entering the query, just to be on the safe side. Does this query work without the where clause? What error is ColdFusion giving? I've had problems in the past using the MAX function and wildcards in a query. Although, you aren't using a wildcard, perhaps you are experiencing a similiar problem here? I imagine the MIN function would produce similiar problems. > and contacts.ContactID = events.EventMainContactID > and venues.EventVenueID = events.EventVenueID > and dates.EventID = events.EventID > ORDER BY EventBeginDate #SortOrder# > > > 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? I'm not sure how two separate tables would solve your problem. -- Jeff Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781 -- Instant Cold Fusion 4.5 | ISBN: 0-07-213238-8 Due out 3rd Quarter 2001 -- DotComIt, LLC database driven web data using ColdFusion, Lotus Notes/Domino -- Half of the Alternative Folk Acoustic Duo called Far Cry Fly http://www.farcryfly.com | http://www.mp3.com/FarCryFly -- Promise me no dead end streets, and I'll guarantee we'll have a road ~~ 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
Re: SQL: Aliases in where clauses.
Chris, As far as I know, aliases are not allowed in SQL 7 where clauses. Nothing about the BOL suggests that as an option either. But, it looks like an aggregate query since you're using MIN and so on, suggesting you might look into a "having" clause. (Although I don't see a "group by" clause, and I see non-aggregate columns, so maybe I'm missing something, and I'll stop here...) Mark [EMAIL PROTECTED] Hi all. I was wondering, is it possible to use aliases in an sql where clause? 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
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: > > > > > >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 > >FROMdbo.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# > > > > > >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
Re: SQL: Aliases in where clauses.
Jeffry Houser wrote: > > That is some select statement. > > I've used aliases (for both tables and fields) in the where clause > in SQL Server 7, and 2000. You didn't mention what database you > were using, although I don't think that should make a difference. Someone else said they weren't available, but I know for a fact that I've used them (or rather, seen them used) in tables. Maybe I'm wrong about the fields. -- Jeff Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781 -- Instant Cold Fusion 4.5 | ISBN: 0-07-213238-8 Due out 3rd Quarter 2001 -- DotComIt, LLC database driven web data using ColdFusion, Lotus Notes/Domino -- Half of the Alternative Folk Acoustic Duo called Far Cry Fly http://www.farcryfly.com | http://www.mp3.com/FarCryFly -- Promise me no dead end streets, and I'll guarantee we'll have a road ~~ 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
RE: SQL: Aliases in where clauses.
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: > > > > > > > > > > > > > > >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 > > > > >FROMdbo.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#) > > >
RE: SQL: Aliases in where clauses.
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: > > > > > > > > >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 > > >FROMdbo.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# > > > > > > > > >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
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: > > > > > > > > > > > >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 > > > >FROMdbo.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# > > > > > > > > > > > >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
