View previous topic :: View next topic
|
Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Team,
After execution of the below query
Code: |
SELECT DISTINCT AB_DT
FROM XYZ
WHERE AB_DT <'03/14/2013'
AND AB_F = 'B'
ORDER BY 1 DESC
WITH UR |
output is
Code: |
AB_DT
----------
2013-03-13
2013-03-12
2013-03-11
2013-03-08
2013-03-07
2013-03-06
2013-03-05
2013-03-04
2013-03-01
2013-02-28
2013-02-27
2013-02-26
2013-02-25
2013-02-22
2013-02-21 |
Now requirement is I want to get 7th row alone from the about result, I did this by using cursor and fetching it 7 times but can you please suggest to do this using single query?
so the output should only be
date |
|
Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006 Posts: 154 Location: US
|
|
|
|
Rohit,
Please try below mentioned query
Code: |
SELECT * FROM
(SELECT DISTINCT AB_DT,DENSE_RANK() OVER (ORDER BY AB_DT DESC) AS RN
FROM XYZ
WHERE AB_DT < '2013-03-14') X
WHERE RN=7
WITH UR |
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Sai,
Perfect, This is working. Thanks.
I hope we can be able to use this in the application programming, but in SPUFI it is working as expected. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Why do you think it won't work with COBOL (application programming)? The only restriction is that 'DB2 should be in NFM'. Apparently you are using NFM in the LPAR you've tried with SPUFI. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
should be more performant :
Code: |
SELECT ab_dt FROM
(SELECT DISTINCT AB_DT FROM XYZ WHERE AB_DT < '2013-03-14' order by ab_dt desc fetch first 7 rows only) X
order by ab_dt asc
fetch first row only
WITH UR |
|
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Thanks GuyC, a nice point you put forward. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
GuyC, Thanks.
Quote: |
Code: |
SELECT ab_dt FROM
(SELECT DISTINCT AB_DT FROM XYZ WHERE AB_DT < '2013-03-14' order by ab_dt desc fetch first 7 rows only) X
order by ab_dt asc
fetch first row only
WITH UR |
|
Can we use host variable instead of 7 in
Quote: |
fetch first 7 rows only |
?
As 7 is just the example I have provided, but this is a dynamically changing value. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Don't take it otherwise - perhaps this is something from your side to research on Rohit, yes? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Anuj, yeah .
And The query provided by Sai gives the correct result but COST*RATE is considerably larger than using the cursors. So I am dropping this plan to use OLAP fuction. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Have you checked with what GuyC has suggested, that should give you better (read it as 'rather less') COST*RATE? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Yes Anuj, shortly I will post the results. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
I am getting the below message when I tried using the host variable instead of hard coded "7",
Code: |
ILLEGAL SYMBOL ":". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <INTEGER> ROW ROWS DB2 SQL PRECOMPILER STATISTICS |
Any more suggestions. |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
Rohit Umarjikar wrote: |
I am getting the below message when I tried using the host variable instead of hard coded "7",
Code: |
ILLEGAL SYMBOL ":". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <INTEGER> ROW ROWS DB2 SQL PRECOMPILER STATISTICS |
Any more suggestions. |
I suggest that this means that you can't use a host variable to supply the number of rows to be fetched. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
yeah, true.
So I have only one choice left to use cursors (ofcource when we talk about better performance). Thanks all. |
|
Back to top |
|
|
|