4 Replies Latest reply on Apr 30, 2007 2:27 PM by Dan Bracuk

    SQL If statments

    noobie2005
      Hi guys,

      I was wondering if inside a CFQUERY statement you could find a way to see if a Database field is empty and set still let it pass, would you use a SQL if statement or the WHERE field IS NULL? How could this be done?
        • 1. SQL If statments
          paross1 Level 2
          Not sure if I understand your question, but with SQL, WHERE field IS NULL would be the way to check.

          Phil
          • 2. Re: SQL If statments
            noobie2005 Level 1
            Sorry, I'll try to better explain myself.

            I have a query that totals all the devices in a given field, but, if that table is empty, CF gives me an error that the variable I set, to see if the amount on hand is less than what is needed, is undefined, and I wanted to know if there was a way around that, so it could just read: "DBTABLE is empty, no records exsist", or something along that line.....

            I hope that's clearer, and wouldn't I just make a statement within the CFIF statement stating that if DBFIELD EQ " " No Records Exsist?
            • 3. SQL If statments
              paross1 Level 2
              You could check the rceordcount variable for your query and if it is <> 0

              <cfquery name="your_query" datasource="dsn">
              SELECT your_fields
              FROM your_table
              WHERE whatever
              </cfquery>

              <cfif your_query.recordcount NEQ 0>
              do whatever you want to do if you have records
              <cfelse>
              your alternative
              </cfif>

              Phil
              • 4. Re: SQL If statments
                Dan Bracuk Level 5
                quote:

                Originally posted by: noobie2005
                Sorry, I'll try to better explain myself.

                I have a query that totals all the devices in a given field, but, if that table is empty, CF gives me an error that the variable I set, to see if the amount on hand is less than what is needed, is undefined, and I wanted to know if there was a way around that, so it could just read: "DBTABLE is empty, no records exsist", or something along that line.....

                I hope that's clearer, and wouldn't I just make a statement within the CFIF statement stating that if DBFIELD EQ " " No Records Exsist?

                What does "totals all the devices in a given field" mean? What sql are you running to determine that?