asked on

reverse logic

Hi there,
I am getting sales over 1 miilion when i run the query below, but i want to reverse the logic

Now if (SUBSTR( CAMPAIGN_CODE, 6, 1 ) = 'Q'
      OR SUBSTR( CAMPAIGN_CODE, 8, 1 ) = 'Q'
      OR (DTL_REV_USD > 1000000.00))) exclude that record

SELECT DETAIL_KEY,OPP_NUM,CAMPAIGN_CODE
,CASE WHEN (OPP_SIEBEL_SALES_STAGE_CODE in ('04','05','06','07','08')
-- AND HAS_CAMPAIGN='Y'
AND FLOW_CODE in ('DRS','DHS','OEM')
AND CURR_COV_TYPE <> 'P'
AND PROD_PLATFORM <> 'Services'
AND DTL_ODDS in (10,25,50,75, 100)
AND (SUBSTR( CAMPAIGN_CODE, 6, 1 ) = 'Q'
      OR SUBSTR( CAMPAIGN_CODE, 8, 1 ) = 'Q'
      OR (DTL_REV_USD > 1000000.00)))
THEN
( DTL_REV_USD )
ELSE
( 0 )
END AS MKT_PIPE_EXCLD_USD FROM DMART_MKT1.STG_OPP_DETAILS
WHERE OPP_NUM = '1-2YK399'
AND DETAIL_KEY ='255U-JO6VL5'
FETCH FIRST 10 ROWS ONLY

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_276102

... or simply put a NOT in front?

NOT (SUBSTR( CAMPAIGN_CODE, 6, 1 ) = 'Q'
OR SUBSTR( CAMPAIGN_CODE, 8, 1 ) = 'Q'
OR (DTL_REV_USD > 1000000.00))) exclude that record

And note that the NOT is outside the parenthesis that combines the three tests.

NOT (A or B or C) should be equivalent to (not A and not B and not C). Changing [=] to [<>] or [>] to [<=] should be the equivalent of negating each condition.

Tom

momi_sabag

if you want to exclude records that has
(SUBSTR( CAMPAIGN_CODE, 6, 1 ) = 'Q'
OR SUBSTR( CAMPAIGN_CODE, 8, 1 ) = 'Q'
OR (DTL_REV_USD > 1000000.00)))

then you have to add it to your where clause
try
SELECT DETAIL_KEY,OPP_NUM,CAMPAIGN_CODE
,CASE WHEN (OPP_SIEBEL_SALES_STAGE_CODE in ('04','05','06','07','08')
-- AND HAS_CAMPAIGN='Y'
AND FLOW_CODE in ('DRS','DHS','OEM')
AND CURR_COV_TYPE <> 'P'
AND PROD_PLATFORM <> 'Services'
AND DTL_ODDS in (10,25,50,75, 100)
THEN
( DTL_REV_USD )
ELSE
( 0 )
END AS MKT_PIPE_EXCLD_USD FROM DMART_MKT1.STG_OPP_DETAILS
WHERE OPP_NUM = '1-2YK399'
AND DETAIL_KEY ='255U-JO6VL5'

AND NOT ((SUBSTR( CAMPAIGN_CODE, 6, 1 ) = 'Q'
OR SUBSTR( CAMPAIGN_CODE, 8, 1 ) = 'Q'
OR (DTL_REV_USD > 1000000.00))) )

FETCH FIRST 10 ROWS ONLY