[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ColdFusion less than statement

Posted on 2012-09-01
15
Medium Priority
?
1,161 Views
Last Modified: 2012-09-01
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>
0
Comment
Question by:DJPr0
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38357172
try this:

            AND REC_QTY is null or REC_QTY  lt  QTY
0
 

Author Comment

by:DJPr0
ID: 38357191
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357206
How did you get AND( (DESCRIP LIKE '%%')) in there?  Can you add a space between AND and the open parenthesis
AND ((DESCRIP LIKE '%%'))
0
Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

 

Author Comment

by:DJPr0
ID: 38357230
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357267
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
 

Author Comment

by:DJPr0
ID: 38357336
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357341
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
 

Author Comment

by:DJPr0
ID: 38357421
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357434
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
 

Author Comment

by:DJPr0
ID: 38357486
It works! well partially - no errors but none of my other search qualifiers work - all records are shown all the time.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38357498
It might be because of the OR.  Try adding parenthesis:
AND (REC_QTY is null OR REC_QTY < QTY)
0
 

Author Comment

by:DJPr0
ID: 38357530
That worked!

What does the parentheses do? Hide it from the SQL statement?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38357539
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
 

Author Closing Comment

by:DJPr0
ID: 38357577
Thanks IrogSinta!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38357895
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question