ColdFusion less than statement

This statement works fine - shows all records if the field R_QTY is null - I would like to add "or less than another field (QTY)":

<cfif val(form.showAll) eq 0>
            AND R_QTY is null
            </cfif>

I tried this statement below, and receive this error message: Syntax error (missing operator) in query expression

<cfif val(form.showAll) eq 0>
            AND REC_QTY is null or less than QTY
            </cfif>
DJPr0Asked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
It might be because of the OR.  Try adding parenthesis:
AND (REC_QTY is null OR REC_QTY < QTY)
0
 
mbizupCommented:
try this:

            AND REC_QTY is null or REC_QTY  lt  QTY
0
 
DJPr0Author Commented:
I still receive the same error:
Syntax error (missing operator) in query  -  SELECT * FROM table WHERE 1=1 AND REC_QTY is null or REC_QTY lt QTY AND( (DESCRIP LIKE '%%')) ORDER BY
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
IrogSintaCommented:
How did you get AND( (DESCRIP LIKE '%%')) in there?  Can you add a space between AND and the open parenthesis
AND ((DESCRIP LIKE '%%'))
0
 
DJPr0Author Commented:
Tried adding the space - same result.
I have a lot of other criteria in this statement that filters multiple fields. The statement works fine with:
<cfif val(form.showAll) eq 0>
            AND REC_QTY is null
            </cfif>


Here is the whole statement:
SELECT *
FROM         table
WHERE 1=1   <cfif discipline1 neq "ALL">
                  AND sip = '#discipline1#'
                  </cfif>
            <!---<cfif val(form.showAll) eq 0>
            AND rec_date is null
            </cfif>--->
           
            <cfif val(form.showAll) eq 0>
            AND REC_QTY is null or REC_QTY lt QTY  
            </cfif>

           
            <cfif discipline2 Is NOT "ALL">
                  AND dis = '#discipline2#'
                  </cfif>
            <cfif discipline3 Is NOT "ALL">
                  AND vendor = '#discipline3#'
                  </cfif>
            <cfif discipline4 Is NOT "ALL">
                  AND pro = '#discipline4#'
                  </cfif>

             <cfif discipline7 Is NOT "ALL">
                  AND con = '#discipline7#'
                  </cfif>
           
            <cfif REQ Is NOT "">
                        <cfif REQ Is NOT "ALL">
                AND REQUIC = '#REQ#'
                        </cfif>      
                </cfif>
           
           
           
            <cfif NSN Is NOT "">
                        <cfif NS Is NOT "ALL">
                AND NSN = '#NS#'
                        </cfif>      
                </cfif>
               
               
               
                  <cfif FindNumber Is NOT "">
                        <cfif FindNumber Is NOT "ALL">
                              <cfif Exact Is NOT "No">
                        AND findno = '#FindNumber#'
                              <cfelse>
                        AND findno LIKE '%#FindNumber#%'
                              </cfif>
                        </cfif>
                  </cfif>
                  AND ((#fieldlist# LIKE '%#Replace(Trim(Keywords), " ", "%' AND #fieldlist# LIKE '%", "ALL")#%'))

ORDER BY findno



</cfquery>
0
 
IrogSintaCommented:
Can you see if it works this way:
<cfif val(form.showAll) eq 0>
            REC_QTY lt QTY  
            </cfif>

If it does, can you try:
<cfif val(form.showAll) eq 0>
            AND (REC_QTY is null) or (REC_QTY lt QTY)  
            </cfif>

Or
<cfif val(form.showAll) eq 0>
            AND IsNull(REC_QTY) or REC_QTY lt QTY  
            </cfif>

If none of the above work, hopefully mbizup or someone else can help as I'm not very familiar with CFML
0
 
DJPr0Author Commented:
Thanks for trying!

None of these work, receive the same error - Could the problem be in QTY field - can this be a null value?
0
 
IrogSintaCommented:
If REC_QTY lt QTY by itself didn't work, then QTY could definitely be the culprit.  Can you add a test to see if it is null before including it in your statement?
0
 
DJPr0Author Commented:
This works:
 AND qty is null
Problem is I cant add to that statement for some reason.

I also tried populating all fields in QTY with the above statements.

Both fields - REC_QTY and QTY are number fields in Access.
0
 
IrogSintaCommented:
Oh wait, a minute, since this is part of the SQL statement and not part of the CFML expression, try this:
AND REC_QTY is null or REC_QTY < QTY
0
 
DJPr0Author Commented:
It works! well partially - no errors but none of my other search qualifiers work - all records are shown all the time.
0
 
DJPr0Author Commented:
That worked!

What does the parentheses do? Hide it from the SQL statement?
0
 
IrogSintaCommented:
No it just prioritizes.
If was told to purchase 1 apple and 1 orange or 1 banana at the grocery, a parenthesis can change the meaning.

(1 apple and 1 orange) or 1 banana
means I can come home with 1 fruit (banana) in the bag or 2 fuits (apple and orange)

1 apple and (1 orange or 1 banana)
means I will come home with 2 fruits, 1 being an apple, and the other could be a banana or an orange.
0
 
DJPr0Author Commented:
Thanks IrogSinta!
0
 
_agx_Commented:
As a general rule, when a query contains both AND and OR operators *always* use parenthesis. It's the only way to guarantee you get the correct results, like IrogSinta said.

See operator precedence:
http://msdn.microsoft.com/en-us/library/ms190276.aspx

            >    AND ((#fieldlist# LIKE ...

As an aside with rampant sql injection threats, data scrubbing and cfqueryparam is a must! You should be using cfqueryparam on all query parameters. Unfortunately it can't be used for column names. If #fieldlist# is a user supplied variable ie form,url,etc... be sure to validate it to protect your database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.