Stupid Q - Want to restrict results where X <> value 1

[CraigBest]

Hi Folks

I know this is a stupid question but I appear to be having a brain cramp this morning. Unfortunately Coffee has not helped me return to clarity today, so I turn to you...

I have a query where I want to return records if the values of either of two separate fields in two different tables are not equal to a specific value.

This is the statement in the WHERE clause that I KNOW is not written properly...

AND (EC.RATING_REFERRAL <> -1 or PU.REVENUE_SHARING <> 1)

There is a proper way to do this, and I know this isn't it. Can someone help?

Thanks



CraigHartz

 

[Dagon]

From what you've stated, it looks OK. However, if either EC.RATING_REFERRAL or PU.REVENUE_SHARING are NULL, they will also fail the condition. You might therefore want to add an NVL condition e.g.

AND (NVL(EC.RATING_REFERRAL,-1) <> -1 or
NVL(PU.REVENUE_SHARING,1) <> 1)

This will decode NULLS to be the values being compared and they will not therefore be rejected.

 

[Dagon]

Got that the wrong way round. You'd want to NVL them to something different to the compared value e.g.

(AND (NVL(EC.RATING_REFERRAL, -9999) <> -1 or
NVL(PU.REVENUE_SHARING, -9999) <> 1)

 

[CraigBest]

Thanks - yes it looks like that will work. I really appreciate it!



CraigHartz

 

[CraigBest]

Well, I tried it - didn't ee this one coming. Both the tables EC and PU are included by outer joins and so cannot be used with an OR operator. Any ideas on how I can get around that?

CraigHartz

 

[Dagon]

The usual technique is to use inline views, but I'd need to see the full SQL to be able to see exactly how it could be done.

 

[CraigBest]

OK, here it is:

SELECT OI.OITEM_ORD_ID ORDER_ID,
  sum(PP.PPOST_APPL_AMT - NVL(CR.CREDIT_AMT,0)) NON_ROYAL_PAYMENT_AMT
  
FROM ORDERS O, ORDER_ITEMS OI, CHARGED_ITEMS CI, PAYMENT_POSTINGS PP, INVOICES IV,
  PREFERRED_PUBLISHERS PU, PROMOTIONS PM, ECOMMERCE_PRODUCT EC, SALEABLE_ITEMS SA, 
  (SELECT - CIA.CIADJ_AMT CREDIT_AMT, CIA.CIADJ_DATE_CREATED CREDIT_DATE, CI.CITEM_OITEM_ID 
  FROM ORDER_ITEMS OI, CHARGED_ITEMS CI, CHARGED_ITEMS_ADJ CIA
  WHERE CIA.CIADJ_CITEM_ID = CI.CITEM_ID
      AND OI.OITEM_ID = CI.CITEM_OITEM_ID
      AND CIA.CIADJ_REASON_CODE IN ('CR', 'PA')
      AND CI.CITEM_TYPE = 'PROD') CR
  
WHERE OI.OITEM_ID = CI.CITEM_OITEM_ID
  AND O.ORD_ID = OI.OITEM_ORD_ID 
  AND CI.CITEM_TYPE = 'PROD'
  AND CI.CITEM_ID = PP.PPOST_CITEM_ID
  AND IV.INVC_ORD_ID = OI.OITEM_ORD_ID 
  AND EXTRACT(YEAR FROM IV.INVC_DATE) = 2007 --p_Report_Year
  AND EXTRACT(MONTH FROM IV.INVC_DATE) <= 3 --p_Report_Month 
  AND IV.INVC_STATUS = 'CMPLT'
  AND OI.OITEM_STATUS IN ('INVCD', 'CREDT')
  AND OI.OITEM_ID = CR.CITEM_OITEM_ID(+)
  AND OI.OITEM_SI_ID = SA.SI_ID
  AND SA.SI_WEB_PROD_GRP = EC.PRODUCT_ID (+)
  AND NVL(OI.OITEM_ROYALTY_PROMO_ID, OI.OITEM_PROMO_ID) = PM.PROMO_ID (+)
  AND PM.PROMO_CODE = PU.PPP_CODE (+)
  AND NVL(EC.RATING_REFERRAL, -9999) <> -1 or NVL(PU.REVENUE_SHARING, -9999) <> 1

  AND O.ORD_ID IN
    (SELECT OI.OITEM_ORD_ID FROM PROMOTIONS PM, ORDER_ITEMS OI WHERE
      NVL(OI.OITEM_ROYALTY_PROMO_ID, OI.OITEM_PROMO_ID) = PM.PROMO_ID
      AND PM.PROMO_ROYALTY_FLAG = 'YES')
      
group by OI.OITEM_ORD_ID;

CraigHartz

 

[Dagon]

I can't test this, so don't know for sure if it will work, but try:

SELECT ORDER_ID,
  sum(PPOST_APPL_AMT - CREDIT_AMT) NON_ROYAL_PAYMENT_AMT
from
(select OITEM_ORD_ID ORDER_ID,
        PP.PPOST_APPL_AMT,
		NVL(CR.CREDIT_AMT,0) as CREDIT_AMT,
		NVL(EC.RATING_REFERRAL, -9999) AS RATING_REFERRAL, 
		NVL(PU.REVENUE_SHARING, -9999) AS REVENUE_SHARING
FROM ORDERS O, ORDER_ITEMS OI, CHARGED_ITEMS CI, PAYMENT_POSTINGS PP, INVOICES IV,
  PREFERRED_PUBLISHERS PU, PROMOTIONS PM, ECOMMERCE_PRODUCT EC, SALEABLE_ITEMS SA, 
  (SELECT - CIA.CIADJ_AMT CREDIT_AMT, CIA.CIADJ_DATE_CREATED CREDIT_DATE, CI.CITEM_OITEM_ID 
  FROM ORDER_ITEMS OI, CHARGED_ITEMS CI, CHARGED_ITEMS_ADJ CIA
  WHERE CIA.CIADJ_CITEM_ID = CI.CITEM_ID
      AND OI.OITEM_ID = CI.CITEM_OITEM_ID
      AND CIA.CIADJ_REASON_CODE IN ('CR', 'PA')
      AND CI.CITEM_TYPE = 'PROD') CR
WHERE OI.OITEM_ID = CI.CITEM_OITEM_ID
  AND O.ORD_ID = OI.OITEM_ORD_ID 
  AND CI.CITEM_TYPE = 'PROD'
  AND CI.CITEM_ID = PP.PPOST_CITEM_ID
  AND IV.INVC_ORD_ID = OI.OITEM_ORD_ID 
  AND EXTRACT(YEAR FROM IV.INVC_DATE) = 2007 --p_Report_Year
  AND EXTRACT(MONTH FROM IV.INVC_DATE) <= 3 --p_Report_Month 
  AND IV.INVC_STATUS = 'CMPLT'
  AND OI.OITEM_STATUS IN ('INVCD', 'CREDT')
  AND OI.OITEM_ID = CR.CITEM_OITEM_ID(+)
  AND OI.OITEM_SI_ID = SA.SI_ID
  AND SA.SI_WEB_PROD_GRP = EC.PRODUCT_ID (+)
  AND NVL(OI.OITEM_ROYALTY_PROMO_ID, OI.OITEM_PROMO_ID) = PM.PROMO_ID (+)
  AND PM.PROMO_CODE = PU.PPP_CODE (+)
  AND O.ORD_ID IN
    (SELECT OI.OITEM_ORD_ID FROM PROMOTIONS PM, ORDER_ITEMS OI WHERE
      NVL(OI.OITEM_ROYALTY_PROMO_ID, OI.OITEM_PROMO_ID) = PM.PROMO_ID
      AND PM.PROMO_ROYALTY_FLAG = 'YES'))
  WHERE NOT (RATING_REFERRAL = -1 AND REVENUE_SHARING = 1)
group by ORDER_ID;

 

[CraigBest]

Yep, looks like that worked - Thanks!

CraigHartz