SQL Query where conditions issue

[Former Member]

Hi Experts,

I trying this below query

SELECT T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType] FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode WHERE T2.[GroupMask] in (4,5) and T1.[OcrCode3] IS NOT NULL and T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

GROUP BY T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType]

Whereas though i have defined where condition for  T1.[OcrCode3] IS NOT NULL . Still it is listing me the null OcrCode3 lines.

What could be issue ?

Thanks in advance,

Regards,
Dwarak

[former_member184146 · Accepted solution]

Hi,

Try this

SELECT T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType]

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode

  WHERE T2.[GroupMask] in (4,5) and (T1.[OcrCode3] IS NOT NULL or T1.[OcrCode3]<>'')  and T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

GROUP BY T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType]

--Manish

[Former Member · Accepted solution]

Hi Manish,

Still the same.  whereas if i give "IS NULL" then it shows me only the null field values. But "IS NOT NULL" is not working  what could be the reason ?

Thanks with regards,

Dwarak

[former_member184146 · Accepted solution]

use only T1.[OcrCode3]<>'' 

SELECT T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType]

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T1.Account = T2.AcctCode

  WHERE T2.[GroupMask] in (4,5) and T1.[OcrCode3]<>''  and T0.[RefDate] >=[%0] AND  T0.[RefDate] <=[%1]

GROUP BY T0.[TransId], T1.[Account], T2.[AcctName], T1.[Debit], T1.[Credit], T1.[OcrCode3], T0.[TransType]

--Manish