Please help with this query.

[si_the_geek]

One thing that is likely to cause a problem at some point (if it isn't already) is the way you have appended Date values to the SQL statement - it is not guaranteed to work correctly, as it depends on the regional settings of the computer your program runs on.

To do it safely, you need to convert the Date into a String of a particular format, as explained in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)


Your question isn't entirely clear, but if I'm understanding you correctly, change this:

AND (t.Tran_Type='D' or t.Tran_Type='P') 
AND (m.CODE = 'BO' or m.CODE = 'BA') 
AND t.Commodity ='C'

..to this:

AND (
      (  (t.Tran_Type='D' or t.Tran_Type='P') AND t.Commodity ='C'  )
     OR 
      (  m.CODE = 'BO' or m.CODE = 'BA'  ) 
    )

(you don't need to put the extra spacing into your code, I just do it like this for clarity)

If you want, you could make that shorter by using IN rather than or's, eg:

AND (
      (  t.Tran_Type IN('D','P') AND t.Commodity ='C'  )
     OR 
      m.CODE IN('BO','BA') 
    )