Results 1 to 14 of 14
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: iteration criteria in a recursive SQL

    Hallo everyone,

    I have successfully run a recursive code with following recursion join criteria:

    Code:
    where
            and ...
            and ...    
            and a.iterationColumn = b.iterationColumn + 1
            and ...
    I recognized, that in column b.iterationColumn on my base database not completely all numbers are adding 1:

    ideal case:
    1 2 3 4 5 6 ... 100
    1 2 3 4 5 6 ... 117
    1 2 3 4 5 6 ... 218

    trouble case:
    1 3 4 5 7 8 ... 109

    As we can see, in the trouble case, the iteration criteria will not be able to iterate, since the join criteria ist
    Code:
    b.iterationColumn + 1
    Thus, I would like to ask for ideas, how I can code the iteration criteria, so that all cases will be covered. Instead of taking the criteria + 1, I am thinking of taking an iteration criteria, which would say: TAKE THE NEXT BIGGER NUMBER (NOT RANDOMLY, BUT THE NEXT BIGGER SMALLEST NUMBER). For example from the above trouble case:
    after 1, the recursive SQL should take 3 instead of randomly take 4 or 5.

    Thank you for your ideas.

    Regards,
    Ratna
    Last edited by ratnalein; 10-17-12 at 05:24.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    can you publish the complete sql and if possible db2 version and platform ?
    and some sample data if possible
    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
    May 2012
    Posts
    155
    Hallo przytula_guy,

    I can show the code, but I can not send some sample data. I am working with DB2 9.7. Plattform Unix

    Code:
    rec (column_01, column_02, iterationColumn, column_04) as (
    select 
            b.column_01,
            b.column_02,
            0 as iterationColumn,
            b.column_04
    from 
        base b
    union all
        select
            r.column_01,
            r.column_02,
            b.iterationColumn,
            r.column_04
        from
            rec r,
            mySchema.table b
        where
            r.column_01 = b.column_01 
            and r.column_02 = b.column_02    
            and r.iterationColumn = b.iterationColumn + 1
            and r.column_04 <= r.column_04
    )
    I think, I have an algorithmic problem to tell the iteration to take the next bigger smallest number instead of + 1

    Thank you.

    Regards,
    Ratna

  4. #4
    Join Date
    May 2012
    Posts
    155
    I am not mistaken, actually I need a scalar function from db2 which says:
    "next bigger number"

    Unfortunately, I havent found this function.

  5. #5
    Join Date
    May 2012
    Posts
    155
    Hallo everyone,

    I tried to use this, but it failed

    Code:
    and min(r.iterationcolumn) > b.iterationcolumn
    The error coming out:
    Code:
    Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903
    Any idea?

    Thank you

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Why not use row_number() function to construct a iterationColumn.
    Something like this:
    Code:
    with b as (
         select base.*, row_number() 
                    over ( partition by column_01, column_02  order by column_04 )
                   as iterationColumn
                from yourtable )
    , 
    rec (column_01, column_02, iterationColumn, column_04) as (
    select 
            b.column_01,
            b.column_02,
            iterationColumn,
            b.column_04
    from 
            b 
            where iterationColumn = 0
    union all
        select
            r.column_01,
            r.column_02,
            b.iterationColumn,
            r.column_04
        from
            rec r,
            b
        where
            r.column_01 = b.column_01 
            and r.column_02 = b.column_02    
            and b.iterationColumn = r.iterationColumn + 1;

  7. #7
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ratnalein View Post
    Hallo everyone,

    I tried to use this, but it failed

    Code:
    and min(r.iterationcolumn) > b.iterationcolumn
    The error coming out:
    Code:
    Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903
    Any idea?

    Thank you
    Checkout LEAD or LAG functions.

    Too bad you can't reveal some sample data that illustrates the problem ...
    --
    Lennart

  8. #8
    Join Date
    May 2012
    Posts
    155
    Hallo fengsun2 and lelle12,

    thank you guys for the hints. I have studied over the hints. I think, in my case, it would be the best to build a rank column in the base table, since:

    Code:
    name        iterationColumn
    
    ratna        1
    ratna        2
    ratna        4
    ratna        6
    somebody     1
    somebody     2
    somebody     3
    ...            ...
    ...            ...
    I would like to build an extra column "rankColumn", so that it looks like:

    Code:
    name        iterationColumn   rankColumn 
     
    ratna        1                      1 
    ratna        2                      2 
    ratna        4                      3
    ratna        6                      4 
    somebody     1                      1
    somebody     2                      2
    somebody     3                      3
    ...            ...                     ...
    ...            ...                     ...
    I will then just iterate with the

    Code:
    rankColumn = iterationColumn + 1
    In order to create this extra column, I have tried this code:
    Code:
    SELECT RANK() OVER(ORDER BY iterationColumn) AS rankColumn 
          FROM mySchema.myTable
    It obviously still doesnt work, because the rank should actually be done in respect to each name values (for ratna, for somebody, and for other names). Could someone please help me?

    Thank you.

    Regards,
    Ratna

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ratnalein View Post
    Hallo fengsun2 and lelle12,

    thank you guys for the hints. I have studied over the hints. I think, in my case, it would be the best to build a rank column in the base table, since:

    Code:
    name        iterationColumn
    
    ratna        1
    ratna        2
    ratna        4
    ratna        6
    somebody     1
    somebody     2
    somebody     3
    ...            ...
    ...            ...
    I would like to build an extra column "rankColumn", so that it looks like:

    Code:
    name        iterationColumn   rankColumn 
     
    ratna        1                      1 
    ratna        2                      2 
    ratna        4                      3
    ratna        6                      4 
    somebody     1                      1
    somebody     2                      2
    somebody     3                      3
    ...            ...                     ...
    ...            ...                     ...
    I will then just iterate with the

    Code:
    rankColumn = iterationColumn + 1
    In order to create this extra column, I have tried this code:
    Code:
    SELECT RANK() OVER(ORDER BY iterationColumn) AS rankColumn 
          FROM mySchema.myTable
    It obviously still doesnt work, because the rank should actually be done in respect to each name values (for ratna, for somebody, and for other names). Could someone please help me?

    Thank you.

    Regards,
    Ratna
    with t(a) as ( values 1,3 ) select a, lead(a,1) over (order by a) as next_a from t
    --
    Lennart

  10. #10
    Join Date
    May 2012
    Posts
    155
    Thank you lelle12,

    would you please tell me, what

    Code:
    values (1,3)
    means?

    Edit:
    I think, it is just a sample data right? I am trying to understand why we need this sample?

    Thank you.

    Regards,
    Ratna
    Last edited by ratnalein; 10-17-12 at 14:07.

  11. #11
    Join Date
    May 2012
    Posts
    155
    I tried this, but it is still wrong I think:

    Code:
    select iterationColumn, lead(iterationColumn) over (order by name) as rankColumn from mySchema.myTable
    Mhh...

  12. #12
    Join Date
    May 2012
    Posts
    155
    I am sorry, the database access is cut for an hour, in the meanwhile I have produced this :

    Code:
    SELECT RANK() OVER (PARTITION BY name ORDER BY iterationColumn) AS rankColumn 
          FROM mySchema.myTable
    what do u think, guys? I still havent tested it.

    Regards,
    Ratna
    Last edited by ratnalein; 10-17-12 at 14:32.

  13. #13
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ratnalein View Post
    Thank you lelle12,

    would you please tell me, what

    Code:
    values (1,3)
    means?

    Edit:
    I think, it is just a sample data right? I am trying to understand why we need this sample?

    Thank you.

    Regards,
    Ratna
    Yes, it is sample data. It is an example on how one can provide sample data that those who are willing to help can use.

  14. #14
    Join Date
    May 2012
    Posts
    155
    Thank you lelle12,

    I still have one question:

    Code:
    ... PARTITION BY givenName, familyName ORDER BY ...
    Is that a wise partition definition? I would like to aim the rank function over the concatination of givenName and familyName. For example:

    givenName familyName iterationColumn rankColumn

    ratna carger 2 1
    ratna carger 3 2
    ratna buztena 3 2
    ratna buztena 1 1

    Is that correct? Unfortunately, I havent got the access to database again, so I cant try it. That s why just a simple question through forum. Thank you.

    Regards,
    Ratna

Posting Permissions

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