5 Replies Latest reply on May 27, 2013 3:18 PM by philmodjunk

    ExecuteSQL problem

    JasonO'Berry

      Title

      ExecuteSQL problem

      Post

           Can anyone explain why this calculation wont work:

           ExecuteSQL ( 

            
           "SELECT sum (l.quantity)
           FROM lineitem AS l 
           Where l.fk_InvoiceNumber = ?
           AND l.frame <> ?  
           AND l.frame <> ?  
           AND l.size  =  ? 
            
           "
            

            

           ; ""; "" ;  INVOICE::pk_invoiceNumber  ;  "No Frame" ; "Rolled Canvas" ; "Small" ).
            
           It works until I enter the last and condition and its parameter. I just cant figure it out. Thanks.

        • 1. Re: ExecuteSQL problem
          philmodjunk

               The "last condition" being And I.Size =  ("Small" ) ?

               How does it fail?

               With a question mark or the wrong value?

               What data type is LIneItem::Size?

          • 2. Re: ExecuteSQL problem
            JasonO'Berry

                 yes, that is the last condition.

                 it fails with a ?. and the data type is text, the same as frame. and the calculation result type is number since it is a sum result.

            • 3. Re: ExecuteSQL problem
              JasonO'Berry

                   It works when i quote the size file like this:

                   ExecuteSQL ( 
                    
                   "SELECT sum (l.quantity)
                   FROM \"lineitem\" AS l 
                   Where l.fk_InvoiceNumber = ?
                   AND l.\"size\" <> ?
                   AND l.\"frame\" <> ?
                   AND l.\"frame\" <> ?
                   "
                    
                   ; ""; "" ;  INVOICE::pk_invoiceNumber  ;  "X-Large";  "No Frame" ;  "Rolled Canvas")
                    
                   What is the reason for this. There is no space in the field name, so I can't explain this behavior.
              • 4. Re: ExecuteSQL problem
                RickWhitelaw

                     Just a shot in the dark. Google SQL reserved words. Perhaps you need to quote them as you have done because one of the field names is a reserved word?

                • 5. Re: ExecuteSQL problem
                  philmodjunk

                       That's it. Size is a reserved word. It's really a good idea to quote all field and table occurrence names in the SQL expression rather thant have to keep checking to see if one of your names runs afoul of this issue.