Adding parameters to ORACLE sql case statement

oracle

I have the following sql query

SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE, SAMPLE.STATUS as SAMP_STATUS, 
CASE 
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%' 
    THEN 'TOP' 
    WHEN SAMPLE.TEXT_ID LIKE '%MID%' 
    THEN 'MIDDLE' 
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%' 
    THEN 'BOTTOM' 
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%' 
    THEN 'RETAIN' ELSE 'ROUTINE' 
END as LOCATION, 
CASE 
    WHEN WORK_CENTER = 'LABWARE' 
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER  
END as PROD_RESOURCE, RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY, RESULT.IN_SPEC 
CASE 
    WHEN SAMPLE.STATUS <> 'X' 
    THEN SAMPLE.STATUS
    WHEN SAMPLE.STATUS <> 'A' 
    THEN SAMPLE_STATUS
END
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT) 
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER) 
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT) 
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION = PRODUCT_SPEC.VERSION) 
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE) 
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE = PRODUCT_SPEC.SPEC_TYPE) 
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS)) WHERE LOT.LOT_NUMBER = '40000165304' AND RESULT.REPORTABLE = 'T' 
    AND SAMPLE.SPEC_TYPE <> 'RETAIN' AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE = 'T' Order by ANALYSIS, LOCATION

I NEED TO ADD CONDITIONS FOR THE sample.status i.e.,
Sample.status <> ‘x’ or Sample.status <> ‘R’

I’m not sure where to add these conditions to?

This still isn't working

    SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE,   SAMPLE.STATUS as SAMP_STATUS,   
CASE   
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%'   
    THEN 'TOP'   
    WHEN SAMPLE.TEXT_ID LIKE '%MID%'   
    THEN 'MIDDLE'   
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%'   
    THEN 'BOTTOM'   
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%'   
    THEN 'RETAIN' ELSE 'ROUTINE'   
END as LOCATION,   
CASE   
    WHEN WORK_CENTER = 'LABWARE'   
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER    
END as PROD_RESOURCE, 
RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY,   RESULT.IN_SPEC   
CASE   
    WHEN SAMPLE.STATUS NOT IN ('X', 'A')  THEN SAMPLE_STATUS  
END  
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT)   
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER)   
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT)   
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION =   PRODUCT_SPEC.VERSION)   
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND   (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE)   
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE =   PRODUCT_SPEC.SPEC_TYPE) 
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS)) 
WHERE LOT.LOT_NUMBER = '40000165304' 
  AND RESULT.REPORTABLE = 'T'   
  AND SAMPLE.SPEC_TYPE <> 'RETAIN' 
  AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE =   'T'   
Order by ANALYSIS, LOCATION  

Best Answer

If you want to exclude some rows, you place the filter condition into the "where" clause.

WHERE LOT.LOT_NUMBER = '40000165304' 
  AND RESULT.REPORTABLE = 'T'   
  AND SAMPLE.SPEC_TYPE <> 'RETAIN' 
  AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE =   'T'   
  and SAMPLE.STATUS NOT IN ('X', 'A')  ----<<<<<<<<<<
Order by ANALYSIS, LOCATION