Using cfif in a where clause

[straffenp]

Hi all,

This question is sort of related to a previous post of mine about querying dates.  I'm trying to use a cfif statement in my where clause to determine if a field is empty or not.  Here's the code:

<cfquery name="GetPastEvents" datasource="DSN">

     SELECT *

     FROM SITE:Calendar

     WHERE DatePart('yyyy', [StartDate]) = <cfqueryparam value="#Dateformat(Today, 'yyyy')#" cfsqltype="CF_SQL_DATE" />

     and <cfif EndDate neq ''>StartDate<cfelse>EndDate</cfif> < <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />

     and Archive = <cfqueryparam value="0" cfsqltype="CF_SQL_INTEGER" />

     ORDER BY StartDate ASC, StartTime ASC

</cfquery>

My issue that that CF tells me EndDate is not defined.  EndDate is a field in the table SITE:Calendar.  There has to be a way to make this work, no?  Thanks!

[Steve Sommers · Accepted solution]

You are mixing ColdFusion variables (which cfif requires) and SQL column names. Think of it this way, all CF tags and function execute before the SQL query is sent to the SQL server. You're mixing this and trying to run the CF statement on the SQL server and thus the failure.

You need something like this:

<cfquery name="GetPastEvents" datasource="DSN">

     SELECT *

     FROM SITE:Calendar

     WHERE DatePart('yyyy', [StartDate]) = <cfqueryparam value="#Dateformat(Today, 'yyyy')#" cfsqltype="CF_SQL_DATE" />

          and (

               (enddate is not null and EndDate < <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />)

               or (enddate is null and StartDate < <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />)

          )

          and Archive = <cfqueryparam value="0" cfsqltype="CF_SQL_INTEGER" />

     ORDER BY StartDate ASC, StartTime ASC

</cfquery>

Also, I think your cfif logic was backwards. Instead of NEQ I think you meant EQ because having an EndDate EQ '' and then comparing EndDate to Today (else clause) doesn't make sense.

[straffenp]

Thank you again, Steve.  Your suggestions solved my problem!