1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error in deriving the logic

Discussion in 'SQL PL/SQL' started by amarbose, May 6, 2011.

  1. amarbose

    amarbose Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am getting problem with the query when I am testing:

    Logic is:
    I am not getting proper output from this query.
    for B=606 and C=286 I am expecting a status of 4 in the output.

    The logic is that I am getting a status of 3 from table 2 and so the status will be 4 in the output.

    for B=706 and C=446 I am expecting a status of 3 in output because no B and C value is present in the table 2.
    if a record with status 4 in table 1 is there for a set it should not appear in the output.

    Other logics are:

    A)when there is a value for column D is 2 and 3 for the maximum date in table 1
    and no matching record is found in the table 2 then a row with addition of date(if 5th march in the current table then it will be 6th March) will come out in the result
    from the table1 with same status.
    +
    B) when there is a value column D is 2 and 3 for the maximum date in the table 1
    and a matching record is found in the table 2 with status 2 and 3
    then a row with addition of date will come out in the result from the table1 but the status will change.
    i)if the status is 2 in table and and status of 2 or 3 in table 2 the resultant status will be (status in table 2 is 2) 3 and (status in table 2 is 3)4
    ii)it the status is 3 in table and status of 2 or 3 in table 2 the resultant status will be (status in table 2 is 2) 3 and (status in table 2 is 3)4
    ++++
    C)When there is status of 4 in table 1 there will no resultant row for that set.
    ++
    D)if record with status 1 is there in the table 2 and no matching records present in the table 1 then in output there will be two rows with status 1 and 2
    if record with status 1,2 is there in the table 2 and no matching records present in the table 1 then in output there will be there rows with status 1 , 2 and 3
    E) records with status 4 will end and will not available in the output.

    status and column d are same and column B and C are common columns


    Code (Text):
    WITH
    table_1 AS
    (SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD') A ,101 b ,777 c ,1 status, 0 cnt23 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),101,777,2, 0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02 ','YYYY-MM-DD'),101,777,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),101,777,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04 ','YYYY-MM-DD'),101,777,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),101,777,2,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),121,577,1, 0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02 ','YYYY-MM-DD'),121,577,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),121,577,4,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),102,778,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),102,778,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),102,778,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03','YYYY-MM-DD'),102,778,3,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04','YYYY-MM-DD'),102,778,3,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05','YYYY-MM-DD'),102,778,3,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01','YYYY-MM-DD'),112,878,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),112,878,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),112,878,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),112,878,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04','YYYY-MM-DD'),112,878,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),112,878,3,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),123,576,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),123,576,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),123,576,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),123,576,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04' ,'YYYY-MM-DD'),123,576,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),123,576,4,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),141,321,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),141,321,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),141,321,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),141,321,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04' ,'YYYY-MM-DD'),141,321,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),141,321,3,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),361,526,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),361,526,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),361,526,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),361,526,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04' ,'YYYY-MM-DD'),361,526,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),361,526,3,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),212,578,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),212,578,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),212,578,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),212,578,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04' ,'YYYY-MM-DD'),212,578,2,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),212,578,2,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),723,476,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-01' ,'YYYY-MM-DD'),723,476,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-02' ,'YYYY-MM-DD'),723,476,2,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-03' ,'YYYY-MM-DD'),723,476,2,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-04' ,'YYYY-MM-DD'),723,476,3,3 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),723,476,4,4 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),706,446,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),706,446,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),706,446,3,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),606,286,1,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),606,286,2,0 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-05' ,'YYYY-MM-DD'),606,286,3,0 FROM dual
    ),
    table_2 AS
    (SELECT TO_DATE('2011-07-06','YYYY-MM-DD') A ,102 b ,778 c ,2 status FROM dual UNION ALL
    SELECT TO_DATE('2011-07-06','YYYY-MM-DD'),371,256,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-06','YYYY-MM-DD'),261,556,1 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-06','YYYY-MM-DD'),261,556,2 FROM dual UNION ALL
    SELECT TO_DATE('2011-07-06','YYYY-MM-DD'),606,286,3 FROM dual
    ),table_3 AS (SELECT 1 A,LEVEL*1 prod FROM dual connect BY LEVEL<=2 UNION ALL
    SELECT 2 A,LEVEL*1 prod FROM dual connect BY LEVEL<=3)








    "derieved column logic:
    current date -date in table1(status=1)
    If there is no date present in table 1 for status=1 then the derieved value will be zero."
    Code (Text):
    2011-07-06  101 777 2   5
    2011-07-06  102 778 3   5
    2011-07-06  112 878 3   5
    2011-07-06  141 321 3   5
    2011-07-06  261 556 1   0
    2011-07-06  261 556 2   0
    2011-07-06  261 556 3   0
    2011-07-06  371 256 1   0
    2011-07-06  371 256 2   0
    2011-07-06  606 286 4   1
    2011-07-06  706 446 3   1
    2011-07-06  212 578 2   5
    2011-07-06  361 526 3   5
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the output you are getting.