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 11  2630 
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
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
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.
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.
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.
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
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.
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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...
|
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
|
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...
|
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
|
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...
| | |
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;
|
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
|
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
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
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...
|
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...
|
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...
|
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...
|
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
|
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...
| |
