449,190 Members | 1,375 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,190 IT Pros & Developers. It's quick & easy.

# Query from ORACLE board is not working in DB2

 P: n/a 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_date) - max(X.yr) as ConYears from (select ID, year(current_date) - 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) - 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....m-db2/200805/1 Jun 27 '08 #1
11 Replies

 P: n/a 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_date) - max(X.yr) as ConYears from (select ID, year(current_date) - 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) - 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 Jun 27 '08 #2

 P: n/a 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_date) - 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_date) -max(X.yr) as ConYears from ( select ID , year(current_date) - 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) -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 atranslation error than debug the semantics.... -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1 Jun 27 '08 #3

 P: n/a 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(START, 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(START, CURRENT DATE, DECIMAL(1, 8, 0))) AS T; So, now we can generate a simple range of dates: SELECT VALUE FROM TABLE(DATES(CURRENT 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(CURRENT 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. Jun 27 '08 #4

 P: n/a 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. Jun 27 '08 #5

 P: n/a 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_DATE - 2 YEARS" instead of CURRENT_DATE, you would get your expected results. Jun 27 '08 #6

 P: n/a 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_date) - max(X.yr) as ConYears from (select ID, year(current_date) - 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 usedCURRENT_DATE.So, there is no consecutive years from the current year(2008) for allIDs.If you used "DATE('2006-01-01')" or "CURRENT_DATE - 2 YEARS" insteadof CURRENT_DATE, you would get your expected results. -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1 Jun 27 '08 #7

 P: n/a 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_date - 2 years) - max(X.yr) as ConYears from (select ID, year(current_date - 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. Jun 27 '08 #8

 P: n/a 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. Jun 27 '08 #9

 P: n/a 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. Jun 27 '08 #10

 P: n/a 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. Jun 27 '08 #11

 P: n/a 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) asConYears 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_yearhaving 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....m-db2/200806/1 Jun 27 '08 #12

### This discussion thread is closed

Replies have been disabled for this discussion.