Retuning uneanted values 1

[nuttyernurse]

Hello all!

I am trying to report on orders using the sql below. The problem is it returns values that are not in the PERF_DEPT_ID or OGROUP_ID criteria. The results of the query are attached as well.

Any help is appreciated!

SELECT
ORDER_ITEM_SEQ,
PERF_DEPT_ID,
OGROUP_ID,
ORDER_NAME,
PREFERRED_DISPLAY_NAME
FROM
CCDEV.O_ITEM
WHERE
PERF_DEPT_ID IN ('BED','CAR','CCL','CM','CMU','CRH','DIET','DIST','DLY','DMGT','END','EQP','HF','IFC','LAB','NEU','NSG','OT','PT','RAD','RSP','RX','SPH','VAS','WOC','XNSG')
AND
OGROUP_ID IN ('BED','CAR','CST','ECHO','EKG','CCL','CM','CMU','CRH','DIET','DIST','DLY','DMGT','END','EQP','HF','IFC','BLBK','CHEM','HEMA','IMMU','LAB','MICR','REF','NEU','NSG','OT','PT','CT','MG','MRI','NM','NP','RF','US','XA','XR','RSP','RX','SLP','VAS','WOC','XNSG')
AND ORDER_NAME NOT LIKE 'ZZ%'
AND ORDER_NAME NOT LIKE 'zz%'
AND INACTIVE_DDT IS NULL or INACTIVE_DDT = 9999999999

 

[gmmastros]

Change this:

[tt]AND INACTIVE_DDT IS NULL or INACTIVE_DDT = 9999999999 [/tt]

to this:

AND [!]([/!]INACTIVE_DDT IS NULL or INACTIVE_DDT = 9999999999[!])[/!]

Whenever you mix AND's with OR's in a where clause, please make sure to use parenthesis so that the logic is correct.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nuttyernurse]

George,

Thank you very much, exactly what I needed.

 

[r937]

you could also replace this --

AND (INACTIVE_DDT IS NULL or INACTIVE_DDT = 9999999999)

with this --

AND COALESCE(INACTIVE_DDT,9999999999) = 9999999999

;-)


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon