Prev: GET SNAPSHOT blows up monitor switches/returns incomplete data
Next: SQL0902C on db2 connect
From: Tonkuma on 24 Nov 2009 10:20
> Thanks, Tonkuma (anata-wa ichi-ban SQL-no sensei desu!) and Ian. I've
Wow! Near perfect Japanese!

anata-wa ichi-ban-no SQL-no sensei desu!
or
anata-wa SQL-no ichi-ban-no sensei desu!

From: Ian on 24 Nov 2009 19:52
esmith2112 wrote:

> We're in one of those binds, where it's less invasive to change the
> code in a stored procedure and push that, rather than to have to file
> the paperwork to open the application code and get permission to take
> an outage to promote it. That's why doing it in the database was
> preferable for the short term.

I know.

If I had a dollar for every time I heard this from app developers
and then had to fix a subsequent performance problem I'd be writing
this message from the beach. Maybe.


From: ChrisC on 2 Dec 2009 12:57
On Nov 23, 5:04 pm, Tonkuma <tonk...(a)fiberbit.net> wrote:
> Another ways would be use of loop by Procedure statements or by
> Recursive CTE.
>

Just for grins, here is an example of doing this via Recursive CTE:

WITH
/**************************************************
***** Start of sample data *****
**************************************************/
sample_data(COL1, COL2, COL3) AS (
VALUES
( 1, 2, date('11/16/2009'))
,( 1, 2, date('11/17/2009'))
,( 2, 300, date('11/18/2009'))
,( 1, 300, date('11/19/2009'))
,( 1, 2, date('11/20/2009'))
) ,
/**************************************************
***** End of sample data *****
**************************************************/
the_goods (col1, col2, col3, group_id) AS (
select * From (select col1, col2, col3, 1 from sample_data fetch first
1 row only) x
UNION ALL
select sd.col1, sd.col2, sd.col3, case when sd.col1 = tg.col1 then
tg.group_id else tg.group_id + 1 end
from the_goods tg, sample_data sd
where tg.col3 + 1 day = sd.col3
)
select * from the_goods
;
---------------------------------------------------------------------------­---


COL1 COL2 COL3 GROUP_ID
----------- ----------- ---------- -----------
1 2 11/16/2009 1
1 2 11/17/2009 1
2 300 11/18/2009 2
1 300 11/19/2009 3
1 2 11/20/2009 3

This assumes that COL3 is always ascending date - if this isn't the
case, then something a little more interesting is needed:

WITH
/**************************************************
***** Start of sample data *****
**************************************************/
sample_data(COL1, COL2, COL3) AS (
VALUES
( 1, 2, date('11/16/2009'))
,( 1, 2, date('11/17/2009'))
,( 2, 300, date('11/18/2009'))
,( 1, 300, date('11/19/2009'))
,( 1, 2, date('11/20/2009'))
) ,
/**************************************************
***** End of sample data *****
***** Now append row numbers *****
**************************************************/
ordered_data(COL1, COL2, COL3, ORDER) AS (
select col1, col2, col3, row_number() over() from sample_data
) ,
/**************************************************
***** Now get actual data *****
**************************************************/
the_goods (col1, col2, col3, group_id, order) as (
select * From (select col1, col2, col3, order, order from ordered_data
fetch first 1 row only) x
UNION ALL
select sd.col1, sd.col2, sd.col3, case when sd.col1 = tg.col1 then
tg.group_id else tg.group_id + 1 end, sd.order
from the_goods tg, ordered_data sd
where tg.order + 1 = sd.order
)
select col1, col2, col3, group_id from the_goods
;

Thanks,
Chris
From: Tonkuma on 2 Dec 2009 17:38
> ordered_data(COL1, COL2, COL3, ORDER) AS (
> select col1, col2, col3, row_number() over() from sample_data
> )
It will be better to specify ordering explicitly, like this:
ordered_data(COL1, COL2, COL3, ORDER) AS (
select col1, col2, col3, row_number() over(ORDER BY col3) from
sample_data
)
First  |  Prev  | 
Pages: 1 2 3
Prev: GET SNAPSHOT blows up monitor switches/returns incomplete data
Next: SQL0902C on db2 connect