Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Cool Unanswered: Complex select (divide & return multiple rows with a single select)

    Hi ,
    I have to write a query where I have to break up an amount into installment amounts w.r.t. the quarter value.

    Like in db we have a rows as

    ID Amt Qtr Date
    ----------------------------------
    1 1000 4 28/05/2009
    3 5000 3 28/05/2009


    Select ( Amt, Qtr, Date ) where ID =1 ;


    Now I have to include this function inside my select statement

    for ( int i=0;i<=qtr;i++ )
    {
    (Divide amt/qtr ) as installmentamt ,
    date as install_date, //first date will display as it is
    date=date + Addmonths(i) // next ones should increment by months
    }


    I am also restricted of not using a stored procedure. Please advice guys.

    Mike.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    String Representations of Dates in DB2 are like:
    Code:
    Table 4. Formats for String Representations of Dates 
    Format Name             | Abbreviation | Date Format | Example
    ------------------------+--------------+-------------+------------
    International Standards |              |             |
     Organization           | ISO          | yyyy-mm-dd  | 1991-10-27
    IBM USA standard        | USA          | mm/dd/yyyy  | 10/27/1991
    IBM European standard   | EUR          | dd.mm.yyyy  | 27.10.1991
    So, I have modified a little your data.
    If I didn't misunderstand your requirement, one example would be:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(ID, Amt, Qtr, Date) AS (
    VALUES
     (1, 1000, 4, '28.05.2009')
    ,(3, 5000, 3, '28.05.2009')
    )
    SELECT id
         , amt / qtr AS installmentamt
         , date      AS install_date
         , CHAR(DATE(date) + i MONTHS, EUR)
                     AS increment_by_months
      FROM test_data
         , LATERAL
           (SELECT i
              FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
             WHERE i <= qtr
           )
     ORDER BY
           id
         , i
    ;
    ------------------------------------------------------------------------------
    
    ID          INSTALLMENTAMT INSTALL_DATE INCREMENT_BY_MONTHS
    ----------- -------------- ------------ -------------------
              1            250 28.05.2009   28.05.2009         
              1            250 28.05.2009   28.06.2009         
              1            250 28.05.2009   28.07.2009         
              1            250 28.05.2009   28.08.2009         
              1            250 28.05.2009   28.09.2009         
              3           1666 28.05.2009   28.05.2009         
              3           1666 28.05.2009   28.06.2009         
              3           1666 28.05.2009   28.07.2009         
              3           1666 28.05.2009   28.08.2009         
    
      9 record(s) selected.
    If maximum value of qty is not known, try this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(ID, Amt, Qtr, Date) AS (
    VALUES
     (1, 1000, 4, '28.05.2009')
    ,(3, 5000, 3, '28.05.2009')
    )
    ,recursive_query(
       id, qtr
      ,i
      ,installmentamt
      ,install_date
      ,increment_by_months) AS (
    SELECT id, qtr
         , 0
         , amt / qtr
         , date
         , DATE(date)
      FROM test_data
    -- WHERE id = 1
    UNION ALL
    SELECT id, qtr
         , i + 1
         , installmentamt
         , install_date
         , increment_by_months + 1 MONTHs
      FROM recursive_query
     WHERE i < qtr
       AND i < 1000000000
    )
    SELECT id
         , installmentamt
         , install_date
         , CHAR(increment_by_months, EUR)
             AS increment_by_months
      FROM recursive_query
     ORDER BY
           id
         , i
    ;
    ------------------------------------------------------------------------------
    
    ID          INSTALLMENTAMT INSTALL_DATE INCREMENT_BY_MONTHS
    ----------- -------------- ------------ -------------------
              1            250 28.05.2009   28.05.2009         
              1            250 28.05.2009   28.06.2009         
              1            250 28.05.2009   28.07.2009         
              1            250 28.05.2009   28.08.2009         
              1            250 28.05.2009   28.09.2009         
              3           1666 28.05.2009   28.05.2009         
              3           1666 28.05.2009   28.06.2009         
              3           1666 28.05.2009   28.07.2009         
              3           1666 28.05.2009   28.08.2009         
    
      9 record(s) selected.

  3. #3
    Join Date
    Mar 2009
    Posts
    73

    Thumbs up

    Hi thanks for the reply ,

    Your SQL skills are amazing and they provide me with great help !
    You are about 99 % right!

    What I meant with quarter number is that the amount gets divided by number of quarters and number of installments for that amount depend on number quarters.

    for 3 quarters 3 installments,
    for 4 quarters 4 installments and so forth.

    Like say for an amount 1000 with 3 quarters and date 01.01.2009

    1st Installment : 333.33 Installment Date : 01.01.2009
    2nd Installment : 333.33 Installment Date : 01.02.2009
    3rd Installment : 333.33 Installment Date : 01.03.2009

    I also didn't found much help on the internet about the LATERAL keyword. Can you please explain me what this statement is doing specially the i(i) ?

    LATERAL
    (SELECT i
    FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
    WHERE i <= qtr
    )


    Thanks and best regards !
    Mike





    ------------------------------------------------------------------
    Quote Originally Posted by tonkuma
    String Representations of Dates in DB2 are like:
    Code:
    Table 4. Formats for String Representations of Dates 
    Format Name             | Abbreviation | Date Format | Example
    ------------------------+--------------+-------------+------------
    International Standards |              |             |
     Organization           | ISO          | yyyy-mm-dd  | 1991-10-27
    IBM USA standard        | USA          | mm/dd/yyyy  | 10/27/1991
    IBM European standard   | EUR          | dd.mm.yyyy  | 27.10.1991
    So, I have modified a little your data.
    If I didn't misunderstand your requirement, one example would be:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(ID, Amt, Qtr, Date) AS (
    VALUES
     (1, 1000, 4, '28.05.2009')
    ,(3, 5000, 3, '28.05.2009')
    )
    SELECT id
         , amt / qtr AS installmentamt
         , date      AS install_date
         , CHAR(DATE(date) + i MONTHS, EUR)
                     AS increment_by_months
      FROM test_data
         , LATERAL
           (SELECT i
              FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
             WHERE i <= qtr
           )
     ORDER BY
           id
         , i
    ;
    ------------------------------------------------------------------------------
    
    ID          INSTALLMENTAMT INSTALL_DATE INCREMENT_BY_MONTHS
    ----------- -------------- ------------ -------------------
              1            250 28.05.2009   28.05.2009         
              1            250 28.05.2009   28.06.2009         
              1            250 28.05.2009   28.07.2009         
              1            250 28.05.2009   28.08.2009         
              1            250 28.05.2009   28.09.2009         
              3           1666 28.05.2009   28.05.2009         
              3           1666 28.05.2009   28.06.2009         
              3           1666 28.05.2009   28.07.2009         
              3           1666 28.05.2009   28.08.2009         
    
      9 record(s) selected.
    If maximum value of qty is not known, try this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(ID, Amt, Qtr, Date) AS (
    VALUES
     (1, 1000, 4, '28.05.2009')
    ,(3, 5000, 3, '28.05.2009')
    )
    ,recursive_query(
       id, qtr
      ,i
      ,installmentamt
      ,install_date
      ,increment_by_months) AS (
    SELECT id, qtr
         , 0
         , amt / qtr
         , date
         , DATE(date)
      FROM test_data
    -- WHERE id = 1
    UNION ALL
    SELECT id, qtr
         , i + 1
         , installmentamt
         , install_date
         , increment_by_months + 1 MONTHs
      FROM recursive_query
     WHERE i < qtr
       AND i < 1000000000
    )
    SELECT id
         , installmentamt
         , install_date
         , CHAR(increment_by_months, EUR)
             AS increment_by_months
      FROM recursive_query
     ORDER BY
           id
         , i
    ;
    ------------------------------------------------------------------------------
    
    ID          INSTALLMENTAMT INSTALL_DATE INCREMENT_BY_MONTHS
    ----------- -------------- ------------ -------------------
              1            250 28.05.2009   28.05.2009         
              1            250 28.05.2009   28.06.2009         
              1            250 28.05.2009   28.07.2009         
              1            250 28.05.2009   28.08.2009         
              1            250 28.05.2009   28.09.2009         
              3           1666 28.05.2009   28.05.2009         
              3           1666 28.05.2009   28.06.2009         
              3           1666 28.05.2009   28.07.2009         
              3           1666 28.05.2009   28.08.2009         
    
      9 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What I meant with quarter number is that the amount gets divided by number of quarters and number of installments for that amount depend on number quarters.

    for 3 quarters 3 installments,
    for 4 quarters 4 installments and so forth.

    Like say for an amount 1000 with 3 quarters and date 01.01.2009

    1st Installment : 333.33 Installment Date : 01.01.2009
    2nd Installment : 333.33 Installment Date : 01.02.2009
    3rd Installment : 333.33 Installment Date : 01.03.2009
    So,
    should
    for ( int i=0;i<=qtr;i++ )
    {
    (Divide amt/qtr ) as installmentamt ,
    date as install_date, //first date will display as it is
    date=date + Addmonths(i) // next ones should increment by months
    }

    be
    for ( int i=0;i<qtr;i++ )
    ...
    }

    ?

    Although LATERAL is one of basic syntax of SQL query,
    many people(even rather experienced engineers) don't know it or don't know effectiveness of it.

    You can find it in table-reference in Subselect in Information Center https://publib.boulder.ibm.com/infoc...v9r5/index.jsp
    or pdf manual "DB2 for Linux, UNIX, and Windows SQL Reference, Volume 1", like this:
    Code:
    nested-table-expression
    
    |--+-----------------------------------------------+--(fullselect)--|
       |         (2)                                   |                 
       '-LATERAL------+------------------------------+-'                 
                      '-| continue-handler |--WITHIN-'
    Notes:
    2 TABLE can be specified in place of LATERAL.


    Correlated references in table-references
    Correlated references can be used in nested table expressions or as arguments to table functions. The basic rule that applies for both these cases is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries. This hierarchy includes the table-references that have already been resolved in the left-to-right processing of the FROM clause. For nested table expressions, the LATERAL keyword must appear before the fullselect. So the following examples are valid syntax:

    Code:
      Example 3:  SELECT d.deptno, d.deptname,
                         empinfo.avgsal, empinfo.empcount
                  FROM department d,
                       LATERAL (SELECT AVG(e.salary) AS avgsal,
                                     COUNT(*) AS empcount
                              FROM employee e           -- department precedes
                              WHERE e.workdept=d.deptno -- and TABLE is
                             ) AS empinfo;              -- specified, so
                                                        -- d.deptno is known
    But the following examples are not valid:

    Code:
      Example 6:  SELECT d.deptno, d.deptname,
                         empinfo.avgsal, empinfo.empcount
                  FROM department d,
                       (SELECT AVG(e.salary) AS avgsal,
                                     COUNT(*) AS empcount
                              FROM employee e           -- department precedes
                              WHERE e.workdept=d.deptno -- but TABLE is not
                             ) AS empinfo;              -- specified, so
                                                        -- d.deptno is unknown
    You will be able to find similar description and examples in "Universal Database for z/OS SQL Reference" and "DB2 for i5/OS SQL Reference".

    ..... Can you please explain me what this statement is doing specially the i(i) ?

    LATERAL
    (SELECT i
    FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
    WHERE i <= qtr
    )
    VALUES used in this example is valid only on DB2 for LUW.
    You can use
    SELECT 0 FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 FROM sysibm.sysdummy1 UNION ALL
    ...
    SELECT 9 FROM sysibm.sysdummy1

    on other platform.

    i(i) is a correlation-clause. It was written to specify column-name i used in WHERE clause and SELECT clause.
    Code:
    correlation-clause
    
       .-AS-.                                                
    |--+----+--correlation-name--+-----------------------+----------|
                                 |    .-,-----------.    |   
                                 |    V             |    |   
                                 '-(----column-name-+--)-'

  5. #5
    Join Date
    Mar 2009
    Posts
    73
    Thanks so much for the reply. It was like a short and very informative session for me

    Is there a way to write a more simpler query which would work both on LUW and iSeries platform which selects records on basis on a Quarter number like for 4 quarters 4 results and so on.

    Also I cant get this to work
    Code:
           select a.genclientcode,a.genclientshare,a.gencommpercent,
    	b.genclientname
    	from administrator.uwftrtyscuty as a, 
    	LATERAL (SELECT i
    	FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
    	WHERE i <= 5)
    	INNER JOIN administrator.uwclient as b on a.genclientcode=b.genclientcode
    	where a.genfortreatysrno=82;
    Last edited by asp_crazy_guy; 07-29-09 at 02:11.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Also I cant get this to work

    Code:
    Code:
        select .genclientcode,a.genclientshare,a.gencommpercent,
    	b.genclientname
    	from administrator.uwftrtyscuty as a, 
    	LATERAL (SELECT i
    	FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
    	WHERE i <= 5)
    	INNER JOIN administrator.uwclient as b on a.genclientcode=b.genclientcode
    	where a.genfortreatysrno=82;
    Some of following examples may be the fix for your error.
    (I'm not sure, because it was not shown exact error code and error messages.)

    If your DB2 doesn't support "(VALUES 0,1,2,3,4,5,6,7,8,9)",
    please see my previous post.
    (DB2 V6R1 for iSeries supports the syntax, according to the manual "DB2 V6R1 for iSeries SQL Reference".)

    Code:
    SELECT a.genclientcode,a.genclientshare,a.gencommpercent,
           b.genclientname
      FROM administrator.uwftrtyscuty as a
      INNER JOIN
           administrator.uwclient as b
       ON  a.genclientcode = b.genclientcode
         , /* LATERAL */
           -- LATERAL is not necessary,
           -- if any column of previous tables is not referenced in subquery.
           (SELECT i
              FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
             WHERE i < 5) -- If you want 5 output rows from one input.
     WHERE a.genfortreatysrno = 82
    ;
    OR
    Code:
    SELECT a.genclientcode,a.genclientshare,a.gencommpercent,
           b.genclientname
      FROM administrator.uwftrtyscuty as a
      INNER JOIN
           (SELECT i
              FROM (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
             WHERE i < 5)
       ON  0 = 0
      INNER JOIN
           administrator.uwclient as b
       ON  a.genclientcode = b.genclientcode
     WHERE a.genfortreatysrno = 82
    ;
    OR
    Code:
    SELECT a.genclientcode,a.genclientshare,a.gencommpercent,
           b.genclientname
      FROM administrator.uwftrtyscuty as a
      INNER JOIN
           (VALUES 0,1,2,3,4,5,6,7,8,9) i(i)
       ON  i < 5
      INNER JOIN
           administrator.uwclient as b
       ON  a.genclientcode = b.genclientcode
     WHERE a.genfortreatysrno = 82
    ;

  7. #7
    Join Date
    Mar 2009
    Posts
    73
    Hi, Trying to migrate this demon query to work on V5R4 but having errors and after lot of head banging I cant figure what is wrong Tonkuma SOS lol
    Code:
    SQL State: 42601
    Vendor Code: -104
    Message: [SQL0104] Token A was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. Cause . . . . . :   A syntax error was detected at token A.  Token A is not a valid token.  A partial list of valid tokens is FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token A. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
    Code:
    select a.genclientcode,a.genclientshare,a.gencommpercent,b.genclientname,i as INSTNO
    
    	from PAKRE.uwftrtyscuty as a
    
    	INNER JOIN PAKRE.uwclient as b on a.genclientcode=b.genclientcode, 
    
    	LATERAL (SELECT i
    
    	FROM (select 0 from sysibm.sysdummy1 UNION ALL 
    
    	           select 1 from sysibm.sysdummy1 UNION ALL
    
                                     select 2 from sysibm.sysdummy1 UNION ALL 
    
     	           select 3 from sysibm.sysdummy1 UNION ALL 
    
    	           select 4 from sysibm.sysdummy1) i(i)
    
    	WHERE i <= 2)
    
    	where a.genfortreatysrno=281 ORDER BY i;

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try
    Code:
    from PAKRE.uwftrtyscuty a
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To remove syntax error, add c then try again.
    Code:
    select a.genclientcode
         , a.genclientshare
         , a.gencommpercent
         , b.genclientname
         , i as INSTNO
      from PAKRE.uwftrtyscuty as a
      INNER JOIN
           PAKRE.uwclient as b
       on  a.genclientcode = b.genclientcode
         , LATERAL
           (SELECT i
              FROM (select 0 from sysibm.sysdummy1 UNION ALL 
                    select 1 from sysibm.sysdummy1 UNION ALL
                    select 2 from sysibm.sysdummy1 UNION ALL 
                    select 3 from sysibm.sysdummy1 UNION ALL 
                    select 4 from sysibm.sysdummy1
                   ) i(i)
             WHERE i <= 2
           ) c
     where a.genfortreatysrno = 281
     ORDER BY i
    ;

Posting Permissions

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