473,721 Members | 1,803 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query from ORACLE board is not working in DB2

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....m-db2/200805/1

Jun 27 '08 #1
11 2659
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
Jun 27 '08 #2
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....m-db2/200805/1

Jun 27 '08 #3
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.
Jun 27 '08 #4
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
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.


Jun 27 '08 #6
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....m-db2/200805/1

Jun 27 '08 #7
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.
Jun 27 '08 #8
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10235
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
7
682745
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time = '01-SEP-02' I'm getting no results. The date_and_time field is formatted like this: 2002-SEP-02 00:01:04
2
3377
by: Allen Anderson | last post by:
Hi, I'm trying to design contact (names and addresses) tables in an Access database. Some of the contacts represent vendors, some are board members of the organization, some are donors, some are neighbors of the organization, some are politicians, etc. Rather than create separate tables for each type of contact, I thought it would be better to have: one table with names/addresses one table with kinds of lists (vendors, board...
12
5280
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
5
1838
by: mantrid | last post by:
Up to the other day I have not bothered protecting my php script on my feedback form against email injection. Howerver, i have had a spammer using it to insert email addresses as cc: bc: into my email field. First I was puzzled why he was doing it as the message being sent was just jibberish. I have recently used a function to protect these fields and send an email back to myself with his details. function below function...
2
5745
by: jmarr02s | last post by:
I am creating a Pass Through Query. Here is my code: SELECT MDSDBA_CINTAKE.RECVDATE, MDSDBA_CINTAKE.CMPSRC, Count(MDSDBA_CINTAKE.CMPSRC) AS CountOfCMPSRC FROM MDSDBA_CINTAKE WHERE RECVDATE>=to_date( '01-09-2005','dd-mm-yyyy') AND RECVDATE<to_date( '01-10-2005','dd-mm-yyyy') AND CMPSRC IS NOT NULL GROUP BY CMPSRC;
2
4528
by: Bob Alston | last post by:
If you have an access form with record source being a straightforward query and where clause in the form definition, will the query be sent to the back end jet/Access database and executed there, withonly the record(s) meeting the criteria being returned to the front end? Is JetShowPlan a good tool to see that this is working? Bob
13
1596
by: lenygold via DBMonster.com | last post by:
I found this problem on ORACLE board. 2 input TABLES: Items Id ItemName 1 Phone 2 Table 3 Lamp 4 TV 5 Stereo
0
8727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9212
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9056
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8002
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4483
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3186
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.