Query from ORACLE board is not working in DB2

[lenygold via DBMonster.com]

Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.

Input Table:

ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999

Requested output:

ID ConYears
1 3
2 2
3 1

The solution uses a CROSS JOIN and a LEFT OUTER JOIN. The CROSS JOIN creates
all possible combinations of ID and year. Then the LEFT OUTER JOIN attempts
to match each such ID and year combination to a row in the data table.

WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, year(current_da te) - max(X.yr) as
ConYears
from (select ID, year(current_da te) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current_da te) - max(X.yr) 0;

The derived table called X contains each combination of ID and year. This is
the left table in the outer join, and it is joined to the data table, such
that it matches the ID and year of the data. Note that it doesn't matter if
more than one row of the data table matches, as is the case in your original
data for ID 1 and year 2006.
Where a matching row is not found, using the IS NULL condition in the WHERE
clause, that combination of ID and year is retained (matching rows are
filtered out), and then, using a GROUP BY, only the maximum year which was
not found for each ID is chosen, and the number of consecutive years
calculated for each ID. Finally, the HAVING clause rejects any IDs like 4
which had 0 consecutive years from the current year.

ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1


When i tested this query: i got an empty output:

ID FIRSTMISSING CONYEARS
----------- ------------ -----------

0 record(s) selected.

Any idea why it is not working?
Thank's in advance. Leny G.

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1

[Serge Rielau]

Re: Query from ORACLE board is not working in DB2

lenygold via DBMonster.com wrote:

Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.
>
Input Table:
>
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999
>
Requested output:
>
ID ConYears
1 3
2 2
3 1
>
The solution uses a CROSS JOIN and a LEFT OUTER JOIN. The CROSS JOIN creates
all possible combinations of ID and year. Then the LEFT OUTER JOIN attempts
to match each such ID and year combination to a row in the data table.
>
WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, year(current_da te) - max(X.yr) as
ConYears
from (select ID, year(current_da te) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current_da te) - max(X.yr) 0;
>
The derived table called X contains each combination of ID and year. This is
the left table in the outer join, and it is joined to the data table, such
that it matches the ID and year of the data. Note that it doesn't matter if
more than one row of the data table matches, as is the case in your original
data for ID 1 and year 2006.
Where a matching row is not found, using the IS NULL condition in the WHERE
clause, that combination of ID and year is retained (matching rows are
filtered out), and then, using a GROUP BY, only the maximum year which was
not found for each ID is chosen, and the number of consecutive years
calculated for each ID. Finally, the HAVING clause rejects any IDs like 4
which had 0 consecutive years from the current year.
>
ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
>
>
When i tested this query: i got an empty output:
>
ID FIRSTMISSING CONYEARS
----------- ------------ -----------
>
0 record(s) selected.
>
Any idea why it is not working?
Thank's in advance. Leny G.
>

Can you share the original Oracle query? May be easier to detect a
translation error than debug the semantics....


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[lenygold via DBMonster.com]

Re: Query from ORACLE board is not working in DB2

Here is original ORACLE QUERY:

Once again we can use the handy-dandy integers table to help. If you don't
have an integers table, you should; it's small, efficient and very useful.

create table integers
(i integer not null )

insert into integers values
(0),(1),(2),(3) ,(4),(5),(6),(7 ),(8),(9)
The integers table can then be used to generate the last 10 years:

select year(current_da te) - i as yr
from integers

yr
2006
2005
2004
2003
2002
2001
2000
1999
1998
1997


select X.ID
, max(X.yr) as FirstMissing
, year(current_da te)
-max(X.yr) as ConYears
from (
select ID
, year(current_da te) - i as yr
from integers
cross
join (
select distinct ID
from datatable
) as I
) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group
by X.ID
having year(current_da te)
-max(X.yr) 0




Serge Rielau wrote:

>Hi everybody!
>This query is supposed to count consecutive years from the current year

>[quoted text clipped - 80 lines]

> Any idea why it is not working?
>Thank's in advance. Leny G.

>
>Can you share the original Oracle query? May be easier to detect a
>translation error than debug the semantics....
>

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1

[Dave Hughes]

Re: Query from ORACLE board is not working in DB2

lenygold via DBMonster.com wrote:

Here is original ORACLE QUERY:
>
Once again we can use the handy-dandy integers table to help. If you
don't have an integers table, you should; it's small, efficient and
very useful.

Nah - much better to have an integers table *function* :-)

-- INTEGERS(START, STOP, STEP)
-- INTEGERS(START, STOP)
-- INTEGERS(STOP)
---------------------------------------------------------------------
-- A table function which returns a single column containing integer
-- values ranging from START to FINISH (inclusive), incrementing (or
-- decrementing) by STEP. If STEP is ommitted, it defaults to 1. If
-- START is also ommitted, it also defaults to 1.
---------------------------------------------------------------------

CREATE FUNCTION INTEGERS(START INTEGER, FINISH INTEGER, STEP INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
-- The I column is only included to prevent warnings about
-- infinite recursion. Its range covers the entire range of
-- 32-bit signed integers hence it shouldn't get in the way
-- of producing a particular result set
WITH RANGE(I, VALUE) AS (
VALUES (INTEGER(-2147483648), START)
UNION ALL
SELECT I + 1, VALUE + STEP
FROM RANGE
WHERE I <= 2147483647 AND VALUE + STEP <= FINISH
)
SELECT VALUE FROM RANGE;

CREATE FUNCTION INTEGERS(START INTEGER, FINISH INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE FROM TABLE(INTEGERS( START, FINISH, 1)) AS T;

CREATE FUNCTION INTEGERS(FINISH INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE FROM TABLE(INTEGERS( 1, FINISH, 1)) AS T;


Now we can do simple things like:

SELECT * FROM TABLE(INTEGERS( 10)) AS T


VALUE
-----------
1
2
3
4
5
6
7
8
9
10

10 record(s) selected.


Or slightly more complicated things like:

SELECT VALUE AS YR
FROM TABLE(INTEGERS( YEAR(CURRENT DATE) - 9, YEAR(CURRENT DATE))) AS T

YR
-----------
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008

10 record(s) selected.


If you want actual DATE values, it's not difficult to adapt the above
to work with dates and durations (for the STEP parameter) encoded as
DECIMAL(8,0):

-- DATES(START, FINISH, STEP)
-- DATES(START, FINISH)
-- DATES(START)
---------------------------------------------------------------------
-- A table function which returns a single column containing dates
-- ranging from START to FINISH (inclusive), incrementing (or
-- decrementing) by STEP. STEP is a DECIMAL(8, 0) value which encodes
-- the step as YYYYMMDD. Hence, 1 increments by a day, 100 increments
-- by a month, 10000 increments by a year. If STEP is ommitted, it
-- defaults to 1. If FINISH is ommitted, it defaults to the current
-- date. Note that this differs from the INTEGERS function above.
-- This parameter layout was chosen as I suspect working with dates
-- in the past is more common than working with dates in the future.
---------------------------------------------------------------------

CREATE FUNCTION DATES(START DATE, FINISH DATE, STEP DECIMAL(8,0))
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
-- The I column is only included to prevent warnings about
-- infinite recursion. The value 37000 was chosen as the limit
-- to allow the function to generate approximately a century's
-- worth of dates. If you need more than this, just increase
-- the limit.
WITH RANGE(I, VALUE) AS (
VALUES (1, START)
UNION ALL
SELECT I + 1, VALUE + STEP
FROM RANGE
WHERE I <= 37000 AND VALUE + STEP <= FINISH
)
SELECT VALUE FROM RANGE;

CREATE FUNCTION DATES(START DATE, FINISH DATE)
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE
FROM TABLE(DATES(STA RT, FINISH, DECIMAL(1, 8, 0))) AS T;

CREATE FUNCTION DATES(START DATE)
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE
FROM TABLE(DATES(STA RT, CURRENT DATE, DECIMAL(1, 8, 0))) AS T;


So, now we can generate a simple range of dates:

SELECT VALUE FROM TABLE(DATES(CUR RENT DATE - 6 DAYS)) AS T

VALUE
----------
25/05/2008
26/05/2008
27/05/2008
28/05/2008
29/05/2008
30/05/2008
31/05/2008

7 record(s) selected.


Or the first date of each year for the last ten years:

SELECT VALUE
FROM TABLE(DATES(
CURRENT DATE - (DAYOFYEAR(CURR ENT DATE) - 1) DAYS - 9 YEARS,
CURRENT DATE, DECIMAL(10000, 8, 0)
)) AS T

VALUE
----------
01/01/1999
01/01/2000
01/01/2001
01/01/2002
01/01/2003
01/01/2004
01/01/2005
01/01/2006
01/01/2007
01/01/2008

10 record(s) selected.


Anyway, regarding the main problem. You stated in the original post you
wanted to do this without OLAP functions. An interesting academic
exercise, certainly but (sticking on my "lazy coder" hat for a second),
is there any reason you don't want to take the easy route?


Cheers,

Dave.

[Tonkuma]

Re: Query from ORACLE board is not working in DB2

ID          FIRSTMISSING CONYEARS  

-----------   ------------               -----------
>
  0 record(s) selected.
>
  Any idea why it is not working?

I guess that the reason of no resulting row is because you used
CURRENT_DATE.
So, there is no consecutive years from the current year(2008) for all
IDs.
If you used DATE('2006-01-01') instead of CURRENT_DATE, you would get
your expected results.

[Tonkuma]

Re: Query from ORACLE board is not working in DB2

ID FIRSTMISSING CONYEARS

----------- ------------ -----------

0 record(s) selected.

Any idea why it is not working?

I guess that the reason of no resulting row is because you used
CURRENT_DATE.
So, there is no consecutive years from the current year(2008) for all
IDs.
If you used "DATE('2006-01-01')" or "CURRENT_DA TE - 2 YEARS" instead
of CURRENT_DATE, you would get your expected results.

[lenygold via DBMonster.com]

Re: Query from ORACLE board is not working in DB2

Hi Tonkuma.
Thank you for your reply.
I tried and no success:

WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, year(current_da te) - max(X.yr) as
ConYears
from (select ID, year(current_da te) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current date - 2 years) - max(X.yr) 0;

ID FIRSTMISSING CONYEARS
----------- ------------ -----------

0 record(s) selected.

Tonkuma wrote:

>ID FIRSTMISSING CONYEARS
>----------- ------------ -----------

>

> 0 record(s) selected.

>

> Any idea why it is not working?

>I guess that the reason of no resulting row is because you used
>CURRENT_DATE .
>So, there is no consecutive years from the current year(2008) for all
>IDs.
>If you used "DATE('2006-01-01')" or "CURRENT_DA TE - 2 YEARS" instead
>of CURRENT_DATE, you would get your expected results.

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1

[Tonkuma]

Re: Query from ORACLE board is not working in DB2

You have changed only HAVING cluase, there are other two CURRENT_DATE.
I tested on DB2 for LUW 9.1. It doesn't support CROSS JOIN. So, I
changed it with traditional way of join(",").
What platform and DB2 Version/Release are you using?

Here is my result:
------------------------------ Commands Entered
------------------------------
WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, year(current_da te - 2 years)
- max(X.yr) as
ConYears
from (select ID, year(current_da te - 2 years) - i as yr
from integers
-- cross join
,
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current date - 2 years) - max(X.yr) 0;
------------------------------------------------------------------------------

ID FIRSTMISSING CONYEARS
----------- ------------ -----------
1 2003 3
2 2004 2
3 2005 1

3 record(s) selected.

[Dave Hughes]

Re: Query from ORACLE board is not working in DB2

lenygold via DBMonster.com wrote:

I have OLAP functions at home DB2 9.5C
But i don't have them at work on mainframe DB2 V8.

Ah, that explains it. Given that Oracle implements the OLAP functions
too, I figured if you were looking for a solution compatible with both
DB2 and Oracle, it'd be easiest to go with that. However, DB2 for z/OS
is indeed a different kettle of fish.

As you state, DB2 8 for z/OS doesn't have any OLAP functions.
Unfortunately, upgrading wouldn't make any difference either: DB2 9 for
z/OS has made a start on implementing them, but so far only has RANK,
DENSE_RANK and ROW_NUMBER - no ability to use the aggregate functions
as yet.

Anyway, it looks like Tonkuma's spotted the error. You'll need to stick
with "," instead of CROSS JOIN as DB2 for z/OS doesn't support the
latter either.


Cheers,

Dave.

[Tonkuma]

Re: Query from ORACLE board is not working in DB2

I have contradictional feeling for Oracle and DB2.
SQL server of MS or Sybase are another things. They are too proprietly
by my impressions.

DB2 is less redundant, more consistent and theoretical. Then it is
more elegant of it's specifications.
Oracle is more practical. There are many breif expressions of which
would be expressed with combinations of another more basic
expressions.

Basically, I like the way of DB2.
Because I need to know and remember less specifications compared with
Oracle SQL to utilise them(Manual "SQL Reference" might be too thick
and heavy even for DB2).
But, sometimes I want to use Oracle's simple expressions.

[Tonkuma]

Re: Query from ORACLE board is not working in DB2

Used MAX( YEAR(DateCol) ) in DATATABLE instead of YEAR(CURRENT DATE -
2 YEARS).
DISTINCT(for id, max_year in inner select) might not be neccesary,
because the columns were grouped in outer select.
------------------------------ Commands Entered
------------------------------
WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, max_year - max(X.yr) as
ConYears
from (SELECT id, max_year, yr
FROM (select DISTINCT
id
, MAX( YEAR(datecol) ) OVER() AS max_year
from datatable) as D
,
LATERAL
(select max_year - i as yr
from integers) as I
) as X
left join
datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID, max_year
having max_year - max(X.yr) 0
;
------------------------------------------------------------------------------

ID FIRSTMISSING CONYEARS
----------- ------------ -----------
1 2003 3
2 2004 2
3 2005 1

3 record(s) selected.

[lenygold via DBMonster.com]

Re: Query from ORACLE board is not working in DB2

Thank you very much Tonkuma.


Tonkuma wrote:

>Used MAX( YEAR(DateCol) ) in DATATABLE instead of YEAR(CURRENT DATE -
>2 YEARS).
>DISTINCT(for id, max_year in inner select) might not be neccesary,
because the columns were grouped in outer select.
>------------------------------ Commands Entered
>------------------------------
>WITH DATATABLE(ID, DateCol) AS
>(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
>select X.ID , max(X.yr) as FirstMissing, max_year - max(X.yr) as
>ConYears
from (SELECT id, max_year, yr
FROM (select DISTINCT
id
, MAX( YEAR(datecol) ) OVER() AS max_year
from datatable) as D
,
LATERAL
(select max_year - i as yr
from integers) as I
) as X
left join
datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID, max_year
>having max_year - max(X.yr) 0
>;
>------------------------------------------------------------------------------
>
>ID FIRSTMISSING CONYEARS
>----------- ------------ -----------
1 2003 3
2 2004 2
3 2005 1
>
3 record(s) selected.

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1