Query Statement 1

[dreadnaught]

I have two rows each either bring back a Y or N. I want to say that if both are Y then do not pull the information. I am using MS Query to pull the information and normally pull using the visual sql instead of writing it. I am not sure how to write this. Hopefully someone can help.

 

[r937]

is there some way you can inspect the query? because we would need to see your current sql to have any hope of suggesting ways to change it to get what you want

also, a few sample rows would help immensely

rudy

http://r937.com/

SQL Consulting

 

[dreadnaught]

Here is the written SQL

SELECT INPOP300.P3PRJ, INPOP300.PURCH, INPOP300.VNDNO, INPOP300.BUYER, INPOP300.HOUSE, INPOP300.OREDT, (QUANO-QBILD)*ECOST, INPOP300.LINE#, INPOP300.ACOST, INPOP300.LSTAT, INPOP100.PSTAT
FROM STB.RMSFILES.INPOP100 INPOP100, STB.RMSFILES.INPOP300 INPOP300
WHERE INPOP300.PURCH = INPOP100.PURCH
GROUP BY INPOP300.P3PRJ, INPOP300.PURCH, INPOP300.VNDNO, INPOP300.BUYER, INPOP300.HOUSE,

what I am trying to say is if INPOP300.LSTAT=Y AND INPOP100.PSTAT=Y THEN DO NOT PULL THE DATA. I HOPE THIS HELPS

 

[r937]

i don't think you need the GROUP BY because you're not aggregating anything

[tt]SELECT INPOP300.P3PRJ
, INPOP300.PURCH
, INPOP300.VNDNO
, INPOP300.BUYER
, INPOP300.HOUSE
, INPOP300.OREDT
, (QUANO-QBILD)*ECOST
, INPOP300.LINE#
, INPOP300.ACOST
, INPOP300.LSTAT
, INPOP100.PSTAT
FROM STB.RMSFILES.INPOP100 INPOP100
, STB.RMSFILES.INPOP300 INPOP300
WHERE INPOP300.PURCH = INPOP100.PURCH
and not
( INPOP300.LSTAT='Y'
AND INPOP100.PSTAT='Y'
)[/tt]

rudy

http://r937.com/

SQL Consulting

 

[dreadnaught]

Thanks,

That seemed to do the trick!!!!