Results 1 to 6 of 6

Thread: a sql problem

  1. #1
    Join Date
    Nov 2011
    Posts
    334

    Unanswered: a sql problem

    hello everyone :
    I am faced to a sql problem.
    Supposed There is a table t1 which has three columms:
    tran_no , data_no, seq and its data looks like this:
    0011, 0001, 001,
    0011, 0001, 002
    0023, 0002, 001
    0023, 0002, 002
    0023, 0002, 003
    0045, 0003, 001

    noticed that each uniq tran_no has a uniq data_no corresponding to it
    and within the same tran_no group ,seq is in ascend order ( increased by 1)
    there is another table T2 which has a column tran_no and some other colums.
    its sample data is :

    0011
    0023
    0023
    0056
    0056

    here is the logic:
    i want to insert all data from t2 into t1.
    when tran_no of two tables not matched , i want to generated a new data_no by increasing the max data_no of t1 in
    sequence order.
    when tran_no matched ,then choose the matched row's data_no and increased the max seq of the same data_no group
    in sequence order.
    the expected result is :
    0011, 0001, 001,
    0011, 0001, 002,
    0011, 0001, 003, <-- added row
    0023, 0002, 001
    0023, 0002, 002
    0023, 0002, 003 <-- added row
    0023, 0002, 004 <-- added row
    0045, 0003, 001
    0056, 0004, 001 <-- added row
    0056, 0004, 002 <-- added row

    i know it can be done with cursor or other language ( java, c++, etc)
    but for some reason , I am requried to do it with one sql statmentment
    Can some body helps me out?
    thx

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    check info center and look for merge statement
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Thanks for your reply。 But Merge can not be used to do it ,
    I wrote the one without it

    WITH T1(C1,C2,C3)
    AS (VALUES ('0011','0001','001'),
    ('0011','0001','002'),
    ('0023','0002','001'),
    ('0023','0002','002'),
    ('0023','0002','003'),
    ('0045','0003','001')),
    T2(C1)
    AS (VALUES ('0011'),
    ('0023'),
    ('0023'),
    ('0056'),
    ('0056'))
    SELECT A.C1,
    (CASE
    WHEN B.C1 IS NULL THEN LPAD(''
    ||(A.RN + C.RN),4,'0')
    ELSE B.C2
    END) AS C2,
    (CASE
    WHEN B.C1 IS NULL THEN LPAD(''
    ||A.SEQ,2,'0')
    ELSE LPAD(''
    ||(A.SEQ + B.SEQ),2,'0')
    END) AS C3
    FROM ((SELECT C1,
    ROW_NUMBER() OVER() AS RN,
    SEQ
    FROM (SELECT C1,
    ROW_NUMBER() OVER(PARTITION BY C1 ) AS SEQ
    FROM T2
    WHERE NOT EXISTS (SELECT 1
    FROM T1
    WHERE T1.C1 = T2.C1))
    UNION ALL
    SELECT C1,
    0 AS RN,
    ROW_NUMBER() OVER(PARTITION BY C1 ) AS SEQ
    FROM T2
    WHERE EXISTS (SELECT 1
    FROM T1
    WHERE T1.C1 = T2.C1)) A
    LEFT JOIN (SELECT C1,
    C2,
    MAX(C3) AS SEQ
    FROM T1
    GROUP BY C1,C2) B
    ON A.C1 = B.C1
    LEFT JOIN (SELECT MAX(C2) AS RN
    FROM T1) C
    ON 1 = 1)

    It looks a bit more complicated。
    I dont know whether there is a more simple way to do it.....

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Code:
    WITH T1(C1,C2,C3)
    AS (VALUES (0011,0001,001),
    (0011,0001,002),
    (0023,0002,001),
    (0023,0002,002),
    (0023,0002,003),
    (0045,0003,001)),
    T2(C1)
    AS (
    VALUES 
    (0011),
    (0023),
    (0023),
    (0056),
    (0056)),
    t3 as -- get max c3 value for c1 
    (
    select c1,c2,max(c3)  maxc3 from t1 where c1 in (select c1 from t2) group by c1,c2
    ),
    t4 as(   -- get max c2 value
    select max(c2) maxc2 from t1
    ),
    t5 as
    (
    
    select t2.c1,c2,rownumber() over (partition by t2.c1) + t3.maxc3 as c3
    from t2,t3 where t2.c1=t3.c1
    union all
    select t2.c1,dense_rank() over (order by t2.c1) + maxc2,rownumber() over (partition by t2.c1) from t2,t4
    where t2.c1 not in (select c1 from t1) 
    )
    select * from t5;
    a possible solution
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    this question also has a more simplified and readable solution:

    Code:
    create table T10( c1 char(4), c2 char(4), c3 char(3) );
    create table T11( c1 char(4) );
    
    insert into t10 values 
     ('0011', '0001', '001')
    ,('0011', '0001', '002')
    ,('0023', '0002', '001')
    ,('0023', '0002', '002')
    ,('0023', '0002', '003')
    ,('0045', '0003', '001');
    
    delete from t11;
    insert into t11 values 
       ('0011')
      ,('0023')
      ,('0023')
      ,('0041')
      ,('0041')
      ,('0042')
      ,('0042');
    
    with temp as (
       select c1,c2,c3,lpad(dense_rank() over ( order by mc,c1 ),4,0) as dr
        from (
          select c1,c2,c3,max(c2) over (partition by (c1)) as mc          
            from ( select c1,c2,c3 
                     from t10 
                   union all
                   select c1,null,null
                     from t11
                 )
              ) 
           )
    select c1,dr
           ,lpad(row_number() over ( partition by c1 order by c3 ),3,0) as rn  
      from temp
    order by dr;
      
    C1   DR   RN
     ---- ---- ---
     0011 0001 001
     0011 0001 002
     0011 0001 003
     0023 0002 001
     0023 0002 002
     0023 0002 003
     0023 0002 004
     0023 0002 005
     0045 0003 001
     0041 0004 001
     0041 0004 002
     0042 0005 001
     0042 0005 002

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Actually, the table design is not in 3rd normal form, which is one of the reasons for the difficulties.
    Nice example in support of table normalisation!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •