2 Replies Latest reply: Nov 23, 2012 12:55 AM by ranit B RSS

    sql query complicated. help required

    797013
      Hai everyone,

      I got stuck with this complicated requirement. Pls give ur suggestion.
      CREATE TABLE POLICY(POLICY_ID NUMBER,EFF_DATE1 DATE,EFF_DATE2 DATE,RATE_DATE1 DATE,RATE_DATE2  DATE,RATE_DATE3 DATE,STATUS VARCHAR2(1));
      
      INSERT INTO POLICY VALUES(1,SYSDATE,SYSDATE-1,SYSDATE,SYSDATE-1,SYSDATE-3,'F');
      
      INSERT INTO POLICY VALUES(2,SYSDATE,SYSDATE-1,SYSDATE,SYSDATE-1,SYSDATE-3,'F');
      select from the table gives me the output as below
      Policy id     Eff_dt1     Eff_dt2     Rate_date1     Rate_date2     rate_date3     level1
      1     24-Nov-12     22-11-2012     22-11-2012     26-11-2012     26-11-2012     F
      2     24-Nov-12     22-11-2012     22-11-2012     26-11-2012     26-11-2012     F
      {code]
      
      my requirement is for each eff_dt , all the rate date should occur in 3 instances  as below
      policy id     eff_dt     rate_date
      1     24-Nov-12     22-11-2012
      1     24-Nov-12     26-11-2012
      1     24-Nov-12     26-11-2012
      1     22-11-2012     26-11-2012
      1     22-11-2012     26-11-2012
      1     22-11-2012     26-11-2012
      2     24-Nov-12     22-11-2012
      2     24-Nov-12     26-11-2012
      2     24-Nov-12     26-11-2012
      2     22-11-2012     26-11-2012
      2     22-11-2012     26-11-2012
      2     22-11-2012     26-11-2012
      how can i achive this?
      
      Kindly help me to sort it out
      
      S                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: sql query complicated. help required
          sukhijank
          Try this
          SELECT a.policy_id, eff_date, rate_date
          FROM   (SELECT policy_id, eff_date1 eff_date FROM policy
                  UNION ALL
                  SELECT policy_id, eff_date2 eff_date FROM policy) a,
                 (SELECT policy_id, rate_date1 rate_date FROM policy
                  UNION ALL
                  SELECT policy_id, rate_date2 rate_date FROM policy
                  UNION ALL
                  SELECT policy_id, rate_date3 rate_date FROM policy) b
          WHERE  a.policy_id = b.policy_id
          • 2. Re: sql query complicated. help required
            ranit B
            Please check this...

            NOTE - Output differing coz used SYSDATE in the INSERT statements.
            with xx as(
                select policy_id,eff_date1 from policy union all
                select policy_id,eff_date2 from policy 
            ),
            x1 as (
                select policy_id,rate_date1 from policy union all
                select policy_id,rate_date2 from policy union all
                select policy_id,rate_date3 from policy 
            )
            select xx.policy_id,eff_date1,rate_date1 from xx,x1
            where xx.policy_id = x1.policy_id
            order by xx.policy_id;
            gives
            1     11/22/2012 12:45:22 AM     11/23/2012 12:45:22 AM
            1     11/23/2012 12:45:22 AM     11/23/2012 12:45:22 AM
            1     11/23/2012 12:45:22 AM     11/22/2012 12:45:22 AM
            1     11/23/2012 12:45:22 AM     11/20/2012 12:45:22 AM
            1     11/22/2012 12:45:22 AM     11/20/2012 12:45:22 AM
            1     11/22/2012 12:45:22 AM     11/22/2012 12:45:22 AM
            2     11/22/2012 12:45:23 AM     11/22/2012 12:45:23 AM
            2     11/23/2012 12:45:23 AM     11/22/2012 12:45:23 AM
            2     11/23/2012 12:45:23 AM     11/20/2012 12:45:23 AM
            2     11/22/2012 12:45:23 AM     11/20/2012 12:45:23 AM
            2     11/23/2012 12:45:23 AM     11/23/2012 12:45:23 AM
            2     11/22/2012 12:45:23 AM     11/23/2012 12:45:23 AM