Show orders below % margin

[arobbo]

Hi guys , i'm struggling with some of the basics of S92.

I'm trying to display all orders below a certain margin in a given month and year.

Not sure if i'm missing something in my layout of the calculation thats giving me the syntax error, can anyone see anything glaringly obviously wrong with my query

Select takenby,totlineamt, orderno, ordersuf, invoicedt, Revcost, (totlineamt - revcost) / totlineamt * 100) Margin  From pub.oeeh where cono = 1 and custno= 1075  and (stagecd = 4 or stagecd = 5)  and month(invoicedt) = 6 AND year(invoicedt) = 2011 AND ((totlineamt - revcost) / totlineamt * 100) <11) and (UCASE(transtype) = 'SO' or UCASE(transtype) = 'CS')

any guidance would be greatly appreciated

Andy

 

[tamhas]

If you gave us some clue about the error you are getting, we might have some clue as to what to look for.

One of the first things I usually do with any SQL that is giving me a problem is to format it like I would any other code, e.g., one field per line in select, one condition per line in where, reasonable indentation, etc. That provides a clue to misplaced () and such.

Also, I then try commenting out pieces to see if I can make it work and then put them back one by one to isolate the problem.

 

[Stefan]

Glaringly obvious, which you would see instantly when formatting your query normally:

Select 
   takenby,
   totlineamt,
   orderno, 
   ordersuf, 
   invoicedt, 
   Revcost, 
   ( totlineamt - revcost ) / totlineamt * 100 ) Margin -- one closing bracket too many
From pub.oeeh 
   where cono   = 1
   and   custno = 1075
   and   (
            stagecd = 4 or
            stagecd = 5
         )
   and   month(invoicedt) = 6 
   AND   year(invoicedt) = 2011 
   AND   ( ( totlineamt - revcost ) / totlineamt * 100 ) < 11 ) -- another closing bracket too many
   and   (
            UCASE(transtype) = 'SO' or 
            UCASE(transtype) = 'CS'
         )

And since I'm sure not everybody is as silly as me as to handformat queries, a quick google search on 'sql query formatter online' resulted in:

http://www.dpriver.com/pp/sqlformat.htm

Which instantly pukes on your two extra brackets. After removing them it results in a nicely formatted query (of which the tabs were lost in translation):

SELECT takenby,
totlineamt,
orderno,
ordersuf,
invoicedt,
revcost,
( totlineamt - revcost ) / totlineamt * 100 margin
FROM pub.oeeh
WHERE cono = 1
AND custno = 1075
AND ( stagecd = 4
OR stagecd = 5 )
AND MONTH(invoicedt) = 6
AND YEAR(invoicedt) = 2011
AND ( ( totlineamt - revcost ) / totlineamt * 100 ) < 11
AND ( Ucase(transtype) = 'SO'
OR Ucase(transtype) = 'CS' )