SQL & PL/SQL

Hi,

I am trying to subtract transaction types from a FACT table using SUM & DECODE functions. I need to substract Transtypes (10201, 10206, 10207, 10212) - (10202, 10206, 10207, 10212)


Query

SELECT
D0.campaign_id,
NVL((NVL(((SUM( DECODE((FACT.transtype_key),(10201),(FACT.Occurred) , (10206), (FACT.Occurred) , (10207), (FACT.Occurred) , (10212), (FACT.Occurred), ( 0) ) ) ) ) , (NULL)) ), ( 0)) - SUM( DECODE((10202),(FACT.Occurred) , (10206),(FACT.Occurred) , (10207),(FACT.Occurred) , (10212),(FACT.Occurred) ) ) )
FROM EPIMART.MESSAGES_CME_0_V FACT, EPIMART.campaign_0_V D0
WHERE FACT.TRANSTYPE_KEY IN (10202, 10207, 10219, 10218, 10210, 10212, 10204, 10201, 10206, 10211)
AND D0.Campaign_key = FACT.campaign_key
AND (D0.campaign_id = '33205')
GROUP BY D0.campaign_id



SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 26 19:13:26 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



Thanks,
Soph

Edited by: Soph on Dec 26, 2012 4:15 PM