DB2 SQL Reporting question!! 1

[db2deep]

Hi -

I want to get the db2 SQL output of a query, WHERE B>=90 and wherever it find the matching condition it should fetch preceding 3 rows.

For Example -
A B
- -
1 5
0 6
5 8
1 10
2 20
3 30
4 98
5 50
11 55
6 60
7 70
8 90
9100

So the output should be based on above criteria -
A B
- -
1 10
2 20
3 30
4 98
11 55
6 60
7 70
8 90

Any help will be appreciated.

Thanks
Sandip

 

[blom0344]

The concept of 'preceding' is not really within the SQL domain.
First of all, what dictates the order by which the initial output is generated?
SQL is pretty straightforward in this matter. If you do not enforce the order in which data is returned then there is no order. If you specify an order than it is by one or more columns, quite unlike the output of your example.

This more or less invalidates the concept of 'preceding'

Can it be done by mere SQL? Possibly, when the order mechanism is revealed. But we need more info and then this still looks more like a candidate for a stored procedure or a multiple-pass approach..



Ties Blom

 

[MarcLodge]

Sorry Sandip, but your example does not make sense. You've said that when column B is greater than or equal to 90, you want the preceeding 3 rows. There are only 2 rows wher column B equals or exceeds 90, yet you show many rows that do not meet the criteria. Please try to explain that which you seek a little more clearly.

Regards,

Marc

 

[db2deep]

Thanks Marc & Ties for your reply!!

Let me try to explain. The row will be selected when B>=90 and when it finds the row, it also selects preceeding 3 rows (order by A asc). As this is a snippet of data which i listed, i wanted to see a SQL logic of how to find a matching row/rows and its 3 earlier rows.

Hope this clears your doubts.

Thanks
Sandip

 

[blom0344]

No, read my post. SQL will return output unsorted or sorted if you apply an explicit sort.
The sort (order by) is either ascending or descending, but you need to specify at least one column to order by.

It will never return data in a predictable order if you do not specify an order. The fact that it does seem to do so is just a matter of the succession in which data is read.
Reorganize the table and the outcome may be very different.

If you store the your original set in an intermediate table or a temp table and add a column with the rownumber() function you can define a complex union that does the trick.
However, this is not staightforward job. Would need some thinking through..

Ties Blom

 

[MarcLodge]

Sandip,
I've still got an issue with your results set, sorry. Clearly only two rows are greater than or equal to 90, the rows with column B equal to 90 and equal to 100. Giving the previous three rows would give:
11 55
6 60
7 70
8 90
9100

Unless you are saying that once the previous three rows have been selected (ie for column B = 90, then 55, 60, and 70) they are no longer available for select, therefore 100 ignores them and selected previous rows.

Is this what you are after?

Marc

 

[sathyarams]

Keep in mind Ties's response on the concept of 'preceding' in SQL.

But, say, if you can 'order by' on a column, then the following might be useful:

Here I have assumed an additional column TS to order the rows. Of course, you can use any column for ordering. As you can see, if you have tab1 as a physical table in your database, then you do not need the temp table tab1 in the expression.
Please note, this does not handle overlapping rows. ie, in your example, B=60 and B=70 rows will be part of the 'preceding' rows of both B=90 and B=100.

with tab1(A,B,TS) as 
(values
(1, 5,current timestamp - 100 minutes),
(0, 6,current timestamp - 98 minutes),
(5, 8,current timestamp - 97 minutes),
(1, 10,current timestamp - 96 minutes),
(2, 20,current timestamp - 95 minutes),
(3, 30,current timestamp - 94 minutes),
(4, 98,current timestamp - 93 minutes),
(5, 50,current timestamp - 92 minutes),
(11, 55,current timestamp - 91 minutes),
(6, 60,current timestamp - 90 minutes),
(7, 70,current timestamp - 89 minutes),
(8 ,90,current timestamp - 88  minutes),
(9,100,current timestamp - 87 minutes)
),
rowseqs(A,B,TS,RID) as 
(
select 
A,B,TS,ROWNUMBER() OVER (ORDER BY TS) AS RID
 from tab1
) 
select A,B,TS from rowseqs where rid in (
select rid
+rid_adjustment 
from rowseqs r
,table(values(0),(-1),(-2),(-3)) as x(rid_adjustment)
 where 
b>=90
)

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[blom0344]

No clue if sathyarams solution is a definitive solution to the original post, but it provided a lot of inspiration (at least for me) A star, just for presenting a possible approach!

Ties Blom

 

[db2deep]

Thanks Satya, the code snippet really helps. Nice work!!

Thanks,
sandip