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:
>
> <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#)
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#
> </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?
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
