question

technette avatar image
technette asked

Combination of And Or Statements

Hi, Is there a way that I can filter results so that the desired data is returned. In the following snippet, I want to eliminate PO's that have been cancelled or closed but I'm still getting those PO's INSERT INTO [ECR_ORDERS] ([Part_ID], [PurchaseOrder]) SELECT DISTINCT POL.PART_ID, POL.PURC_ORDER_ID FROM PURC_ORDER_LINE AS POL INNER JOIN WORK_ORDER AS W ON POL.PART_ID = W.PART_ID INNER JOIN PURCHASE_ORDER AS PO ON POL.PURC_ORDER_ID = PO.ID WHERE (NOT(PO.STATUS ='Complete')) or (NOT(PO.STATUS='Cancelled')) and (POL.PART_ID = @PartNo)
orand
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
Try this: INSERT INTO [ECR_ORDERS] ( [Part_ID] , [PurchaseOrder] ) SELECT DISTINCT POL.PART_ID , POL.PURC_ORDER_ID FROM PURC_ORDER_LINE AS POL INNER JOIN WORK_ORDER AS W ON POL.PART_ID = W.PART_ID INNER JOIN PURCHASE_ORDER AS PO ON POL.PURC_ORDER_ID = PO.ID WHERE PO.STATUS NOT IN ( 'Complete', 'Cancelled' ) AND POL.PART_ID = @PartNo; Just keep in mind that NOT IN won't return any NULL rows if ANSI_NULL is ON.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
@technette Others have shown "how", let me say "why". Your filter clause: WHERE (NOT(PO.STATUS ='Complete')) or (NOT(PO.STATUS='Cancelled')) and (POL.PART_ID = @PartNo) Therefore it basically says "Give me all the results that are: not 'Complete' and @PartNo **OR** not 'Cancelled' and @PartNo" Since Status can only NOT be one of them the other value is valid. You need the change so your pseudo code is: "Give me all the results that are: not 'Complete' and @PartNo **AND** not 'Cancelled' and @PartNo" Which would be something akin to: WHERE (NOT PO.STATUS ='Complete' AND NOT PO.STATUS='Cancelled') and (POL.PART_ID = @PartNo)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette avatar image
technette answered
Thank you for your help!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.