Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

reverse logic

Posted on 2008-11-14
3
Medium Priority
?
308 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:nav29
3 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 22961382

I'm not exactly sure what you're asking. But, if you're simply trying to reverse the logic of this:

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

Then, you could do this:

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

HTH,
DaveSlash
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22964213
... 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
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22969888
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question