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)
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.
@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)
Thank you for your help!
15 People are following this question.
