Forum Stats

  • 3,851,925 Users
  • 2,264,053 Discussions
  • 7,904,906 Comments

Discussions

Can this Query be simplified?

629987
629987 Member Posts: 34
edited Oct 13, 2008 2:23AM in SQL & PL/SQL
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Tagged:

Answers

  • yinghan
    yinghan Member Posts: 5
    I think it's simple enough
  • 337410
    337410 Member Posts: 483
    edited Oct 13, 2008 2:23AM
    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
    649364 Member Posts: 114
    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 &lt;&gt; 'DUMMY'
    THEN A.PAY_LEVEL1_ATTUID
    WHEN A.PAY_LEVEL2_ATTUID IS NOT NULL AND A.PAY_LEVEL2_ATTUID &lt;&gt; 'DUMMY'
    THEN A.PAY_LEVEL2_ATTUID
    WHEN A.PAY_LEVEL3_ATTUID IS NOT NULL AND A.PAY_LEVEL3_ATTUID &lt;&gt; '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 &gt;= '01-JUN-08'
    AND A.CYCLE_DATE &lt;= '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.
This discussion has been closed.