[CLOSED] Compound Where Statement Not Working

November 05, 2012, 04:05 PM

Why does this happen?

TABLE FILE DMDOR
PRINT
...
WHERE DORTYPE EQ 'DEPT';
WHERE DORCP EQ '&MONTH';
WHERE DORCY EQ '&YEAR';
WHERE CO EQ '&CO';
WHERE DEPTNUM EQ '&DEPT';
WHERE (ACCTTYPE NE 'Deductions' AND ACCTSECT NE 'Profit &| Loss');
END

yields

SELECT ...
FROM DMDOR T1
WHERE (T1."ACCTSECT" <> 'Profit & Loss') 
AND (T1."ACCTTYPE" <> 'Deductions') 
AND (T1."DEPTNUM" ='816000') 
AND (T1."CO" = '01') 
AND (T1."DORCY" = '2012') 
AND (T1."DORCP" = '09') 
AND (T1."DORType" = 'DEPT');

Instead of this?

SELECT ...
FROM DMDOR T1
WHERE ( (T1."ACCTSECT" <> 'Profit & Loss') AND (T1."ACCTTYPE" <> 'Deductions') )
AND (T1."DEPTNUM" ='816000') 
AND (T1."CO" = '01') 
AND (T1."DORCY" = '2012') 
AND (T1."DORCP" = '09') 
AND (T1."DORType" = 'DEPT');

- ABT

This message has been edited. Last edited by: Kerry,

------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro

November 05, 2012, 04:57 PM

Severus.snape

If I am not wrong.. isn't that what the translator is supposed to do. In effect that is what the compound WHERE clause is doing. Unless there is an OR in the compound WHERE, it should not matter. Both of them will have same result set.

thanks
Sashanka