This discussion is archived
2 Replies Latest reply: Nov 22, 2012 10:55 PM by ranit B RSS

sql query complicated. help required

797013 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points