Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Can this Query be simplified?

629987Oct 13 2008 — edited Oct 13 2008
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 10 2008
Added on Oct 13 2008
3 comments
204 views