Whereclause question

[knacath]

Hi folks,

I want to exclude rows if two columns in the whereclause have a specific value. For eg., I want to exclude rows with Column A =1 and column B = 2. However, I want this to happen only when both of them have these values, not just one, so a simple where Column A <> 1 and column B <> 2 will not work. Greatly appreciate any help. Thanks.

 

[Cagliostro]

Can you put all the SQL script? John Fill

ivfmd@mail.md

 

[knacath]

SELECT LIB900BOFA.INGLPF.INTTYP, LIB900BOFA.INGLPF.DPTPCT, LIB900BOFA.INGLPF.OPNAME, LIB900BOFA.INGLPF.CATDES, LIB900BOFA.INGLPF.IPFLAG, MST900BOFA.GLMTPF.FSSEQ, LIB900BOFA.INDSPF.ID, LIB900BOFA.INGLPF.ACTNM2, LIB900BOFA.INDSPF.DESCRIPTION1, LIB900BOFA.INDSPF.DESCRIPTION2, LIB900BOFA.INDSPF.DESCRIPTION3, LIB900BOFA.INDSPF.DESCRIPTION4, LIB900BOFA.INDSPF.DESCRIPTION5, LIB900BOFA.INDSPF.DESCRIPTION6, LIB900BOFA.INDSPF.DESCRIPTION7, LIB900BOFA.INGLPF.ACTNM1, LIB900BOFA.INGLPF.ACTNM3, LIB900BOFA.INGLPF.ACTNM4, LIB900BOFA.INGLPF.ACTNM5, LIB900BOFA.INGLPF.UNTTYP, LIB900BOFA.INGLPF.DEPNI, LIB900BOFA.INGLPF.UNTGRS, LIB900BOFA.INGLPF.WLPSCD, LIB900BOFA.INGLPF.CATGOR, LIB900BOFA.INGLPF.GEN, LIB900BOFA.INGLPF.PRPDES, LIB900BOFA.INGLPF.CSNAME, LIB900BOFA.INGLPF.WLST, LIB900BOFA.INGLPF.AMT, LIB900BOFA.INGLPF.SUB, LIB900BOFA.INGLPF.PRPRTY, LIB900BOFA.INGLPF.CUSACC, LIB900BOFA.INGLPF.JEDATE



FROM LIB900BOFA.INGLPF, LIB900BOFA.INDSPF, MST900BOFA.GLMTPF, LIB900BOFA.LNDXMOP


WHERE MST900BOFA.GLMTPF.GEN = LIB900BOFA.INGLPF.GEN AND MST900BOFA.GLMTPF.SUB = LIB900BOFA.INGLPF.SUB AND LIB900BOFA.INGLPF.PRPRTY = LIB900BOFA.LNDXMOP.PROPERTY AND LIB900BOFA.LNDXMOP.ID = LIB900BOFA.INDSPF.ID AND LIB900BOFA.INGLPF.GLCPNY = 900 AND LIB900BOFA.INDSPF.KEYFLAG = 'M' AND MST900BOFA.GLMTPF.GLCPNY = 900

To the whereclause I need to add where (LIB900BOFA.INGLPF.PRPRTY = 8888888 and LIB900BOFA.INDSPF.ID = 5999999) and exclude rows only when both conditions are met

 

[tlbroadbent]

The following works in SQL Server 7 and Access 2000.

Select * From table1
Where Not (ColA=1 And ColB=2)

The next query also works in Access.

Select * From table1
Where (ColA=1 And ColB=2) = 0
Terry

;-) USER, n.: The word computer professionals use when they mean &quot;idiot.&quot; -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[karluk]

Another possibility is

where (LIB900BOFA.INGLPF.PRPRTY <> 8888888 or LIB900BOFA.INDSPF.ID <> 5999999)

The trick is to replace &quot;and&quot; with &quot;or&quot;. That selects only those rows with at least one value different from the ones you want to exclude.

 

[Cagliostro]

knacath, what is the problem, the query doesn't run or you get strange results? John Fill

ivfmd@mail.md

 

[knacath]

John,

It works using tlbroadbent's first suggestion. Thanks to everyone who responded.