2 Replies Latest reply on Aug 27, 2013 10:51 AM by straffenp

    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!

        • 1. Re: Using cfif in a where clause
          Steve Sommers Level 4

          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.

          • 2. Re: Using cfif in a where clause
            straffenp Level 1

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