Can this Query be simplified?

629987

Anyone can simplify this query?

SELECT
  A.PROD_GROUP AS REVENUE_TYPE
 ,A.AGENT_ID
 ,TO_CHAR(A.CYCLE_DATE,'MM/DD/YYYY') AS CYCLE_DATE ,'B'  AS MARKET_SEG 
 ,B.AGENT_NAME ,A.PAY_LEVEL1_ATTUID AS ATTUID1 ,A.REVENUE_PAID + 
  A.REVENUE_RECOUP AS REVENUE
 ,A.SMART_ID    
 ,A.PAY_LEVEL1_ATTUID
 ,A.PAY_LEVEL2_ATTUID
 ,A.PAY_LEVEL3_ATTUID 
 FROM CNT700_ACES_AGENT_COMP A,
            CNT120_AGENT B 
 WHERE 
 A.AGENT_ID = B.AGENT_ID
 AND A.CYCLE_DATE >= '01-JUN-08'
 AND A.CYCLE_DATE <= '30-JUN-08'
 AND A.SUBSID_CD = 'EAST'
 AND A.PAY_LEVEL1_ATTUID IS NOT NULL
 AND A.PAY_LEVEL1_ATTUID <> 'DUMMY'
 
 UNION ALL
 
 SELECT 
  A.PROD_GROUP AS REVENUE_TYPE
 ,A.AGENT_ID
 ,TO_CHAR(A.CYCLE_DATE,'MM/DD/YYYY') AS CYCLE_DATE
 ,'B'  AS MARKET_SEG
 ,B.AGENT_NAME
 ,A.PAY_LEVEL2_ATTUID AS ATTUID1
 ,A.REVENUE_PAID + A.REVENUE_RECOUP AS REVENUE
 ,A.SMART_ID    
 ,A.PAY_LEVEL1_ATTUID
 ,A.PAY_LEVEL2_ATTUID
 ,A.PAY_LEVEL3_ATTUID
 FROM CNT700_ACES_AGENT_COMP A,
            CNT120_AGENT B 
 WHERE 
 A.AGENT_ID = B.AGENT_ID
 AND A.CYCLE_DATE >= '01-JUN-08'
 AND A.CYCLE_DATE <= '30-JUN-08' 
 AND A.SUBSID_CD = 'EAST'
 AND A.PAY_LEVEL2_ATTUID IS NOT NULL
 AND A.PAY_LEVEL2_ATTUID <> 'DUMMY'
 
 UNION ALL
 
 SELECT 
  A.PROD_GROUP AS REVENUE_TYPE
 ,A.AGENT_ID
 ,TO_CHAR(A.CYCLE_DATE,'MM/DD/YYYY') AS CYCLE_DATE
 ,'B'  AS MARKET_SEG
 ,B.AGENT_NAME
 ,A.PAY_LEVEL3_ATTUID AS ATTUID1
 ,A.REVENUE_PAID + A.REVENUE_RECOUP AS REVENUE
 ,A.SMART_ID    
 ,A.PAY_LEVEL1_ATTUID
 ,A.PAY_LEVEL2_ATTUID
 ,A.PAY_LEVEL3_ATTUID
 FROM CNT700_ACES_AGENT_COMP A,
            CNT120_AGENT B 
 WHERE 
 A.AGENT_ID = B.AGENT_ID
 AND A.CYCLE_DATE >= '01-JUN-08'
 AND A.CYCLE_DATE <= '30-JUN-08' 
 AND A.SUBSID_CD = 'EAST'
 AND A.PAY_LEVEL3_ATTUID  IS NOT NULL
 AND A.PAY_LEVEL3_ATTUID <> 'DUMMY'
 ORDER BY
          3,
          9,
          2,
          8,  
          1,
          6,
          10,
          11{code}

Thanks in advance                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

yinghan

I think it's simple enough

337410

Hi,

SELECT A.PROD_GROUP AS REVENUE_TYPE,
       A.AGENT_ID,
       TO_CHAR(A.CYCLE_DATE, 'MM/DD/YYYY') AS CYCLE_DATE,
       'B' AS MARKET_SEG,
       B.AGENT_NAME,
       COLUMN_VALUE AS ATTUID1,
       A.REVENUE_PAID + A.REVENUE_RECOUP AS REVENUE,
       A.SMART_ID,
       A.PAY_LEVEL1_ATTUID,
       A.PAY_LEVEL2_ATTUID,
       A.PAY_LEVEL3_ATTUID
  FROM CNT700_ACES_AGENT_COMP A, CNT120_AGENT B
	     , TABLE(sys.KU$_VCNT (A.PAY_LEVEL1_ATTUID,A.PAY_LEVEL2_ATTUID,A.PAY_LEVEL3_ATTUID))
 WHERE A.AGENT_ID = B.AGENT_ID
   AND A.CYCLE_DATE >= '01-JUN-08'
   AND A.CYCLE_DATE <= '30-JUN-08'
   AND A.SUBSID_CD = 'EAST'
   AND A.PAY_LEVEL1_ATTUID IS NOT NULL
   AND A.PAY_LEVEL1_ATTUID <> 'DUMMY'

Note: Untested.

{message:id=2769016}
Reference Collection Types.

649364

I do not see any difference between the 3 UNION ALL queries, except for the one field with alias ATTUID and it looks pretty fine. However if you want to shorten it further, one way of doing it is by replacing A.PAY_LEVEL1_ATTUID AS ATTUID1 with -

(SELECT CASE
WHEN A.PAY_LEVEL1_ATTUID IS NOT NULL AND A.PAY_LEVEL1_ATTUID <> 'DUMMY'
THEN A.PAY_LEVEL1_ATTUID
WHEN A.PAY_LEVEL2_ATTUID IS NOT NULL AND A.PAY_LEVEL2_ATTUID <> 'DUMMY'
THEN A.PAY_LEVEL2_ATTUID
WHEN A.PAY_LEVEL3_ATTUID IS NOT NULL AND A.PAY_LEVEL3_ATTUID <> 'DUMMY'
THEN A.PAY_LEVEL3_ATTUID
END CASE ATTUID
FROM CNT700_ACES_AGENT_COMP A,
CNT120_AGENT B
WHERE A.AGENT_ID = B.AGENT_ID
AND A.CYCLE_DATE >= '01-JUN-08'
AND A.CYCLE_DATE <= '30-JUN-08'
AND A.SUBSID_CD = 'EAST' )

In this way it may be reduced to a single query instead of 3. Not tested, hence please check for any syntax errors.