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

asp_crazy_guy · #1 (**permalink**) 07-09-09, 00:54

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.

tonkuma · #2 (**permalink**) 07-09-09, 06:22

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.

asp_crazy_guy · #3 (**permalink**) 07-10-09, 06:35

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.

tonkuma · #4 (**permalink**) 07-10-09, 08:39

Quote:

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".

Quote:

..... 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-+--)-'

asp_crazy_guy · #5 (**permalink**) 07-29-09, 01:01

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;

tonkuma · #6 (**permalink**) 07-30-09, 14:34

Quote:

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
;

asp_crazy_guy · #7 (**permalink**) 01-28-10, 02:54

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;

n_i · #8 (**permalink**) 01-28-10, 06:50

Try

Code:

from PAKRE.uwftrtyscuty a

tonkuma · #9 (**permalink**) 01-28-10, 07:22

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech