SQL Query 1

[sathyarams]

V7.2 FP9 , AIX

select * from sn2

TS VAL
-------- -----------
17:13:48 8
17:14:03 10
17:14:09 15

3 record(s) selected.


I'm looking at getting a result like :

TSDIFF VALDIFF
---------------------
15 2
6 5

TSDIFF 15 is 17:14:03 - 17:13:48 seconds and the corresponding VALDIFF 2 is 10-8 ...

Can anyone help, please ?

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[sathyarams]

I should have told ..

I have quoted a simple thing with one column(other than timestamp) ... Actually I will want to have about half-a-dozen columns like this ...

Thanks for your time

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Sathyaram,
I'm not sure I understand what exactly it is you are after.... In you example, you show three rows as the input, but in the output, you only show two rows, and from this I can't really work out what it is you are trying to do. Can you give a little more info.

Thanks

Marc

 

[blom0344]

Yes, I see what you want to achieve.
A possible strategy would be use two temp subsets that hold rank columns like:

rank() over (order by TS) as #rank1 and:
rank()-1 over (order by TS) as #rank2

join the subsets over the ranking columns and you can perform scalar functions over the resultset.

I am going to try and see if I can get it to work...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Sorry, the syntax for the rank() was not correct,

This piece of SQL did run as expected:

table:

key value
2 4
3 9
4 16
5 25
6 36
7 49

SELECT TEMP.KEY, (TEMP.KEY - TEMP2.KEY) AS KEY_DIFF, (TEMP.VALUE - TEMP2.VALUE) AS VAL_DIFF FROM
(Select
KEY,VALUE,RANK() OVER (ORDER BY KEY) AS #RANK1 from TARGET.SATHYARAMS_1909) TEMP,
(Select
KEY,VALUE,(RANK() OVER (ORDER BY KEY)) + 1 AS #RANK2 from TARGET.SATHYARAMS_1909) TEMP2
WHERE TEMP.#RANK1 = TEMP2.#RANK2

gave:

3 1 5
4 1 7
5 1 9
6 1 11
7 1 13

Have a nice weekend..............


T. Blom
Information analyst
tbl@shimano-eu.com

 

[sathyarams]

Thanks Ties for your reply and Marc for your interest ..

A similar solution was suggested in dbforums using rownumber ... You can have a look at it in

http://dbforums.com/showthread.php?threadid=921672

Another forum member had posted a followup question ... If there is an additional field for category, how to extract the same info based on category ...

TS VAL CATEGORY
-------- ------------------------
17:13:48 8 A
17:14:03 10 A
17:15:03 14 A
17:13:48 15 B
17:14:03 18 B
17:15:03 25 B


Output :

TSDIFF VALDIFF CATEGORY
----------------------------------
15 2 A
60 4 A
15 3 B
60 7 B

Thanks

Sathyaram


More DB2 questions answered at

http://www.dbforums.com/f8

 

[blom0344]

Sathyarams, thanks for the star on this one. The solution with the rownumber() would be even better I think, at least more straightforward. If I checked the solution on the the grouping by on the category part, I'll post in dbforums. Alas ,no DB2 available at home..........

T. Blom
Information analyst
tbl@shimano-eu.com