| View previous topic :: View next topic |
| Author |
Message |
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
 Posted: Fri May 11, 2007 7:25 am Post subject: Can this be done throught SPUFI |
 |
|
Hi
I have a table EMP with columns EMP_NAME and EMP_SALARY,
I want to have a SQL query in SPUFI to get exactly 50th row of the table.
Is this possible in SPUFI or should I use cursors?. Can anybody please help me.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
 |
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Fri May 11, 2007 7:29 am Post subject: |
|
|
Do you mean the 50th row entered into the table or the 50th row that DB2 returns - they may not be the same. I would look at the syntax for SELECT and see if it has an option to return the nth row only (where n is greater than 1)
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri May 11, 2007 7:46 am Post subject: |
|
|
psmadhusudhan,
Try this
| Code: |
SELECT EMP_NAME
,EMP_SALARY
FROM EMP A,
TABLE (SELECT COUNT(*)+1 AS ROW#
FROM EMP B
WHERE B.EMP_NAME < A.EMP_NAME) AS TEMP_TAB
WHERE ROW# = 50
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Mon May 14, 2007 3:11 am Post subject: |
|
|
hi kolusu
I had tried with ur systax on my table as below
| Code: |
SELECT POL_POLICY_REF_S
FROM NUDW6.TDWDPOL A
TABLE (SELECT COUNT(*)+1 AS ROW#
FROM NUDW6.TDWDPOL B
WHERE B.POL_POLICY_REF_S < A.POL_POLICY_REF_S) AS TEMP_TAB
WHERE ROW# = 50
;
|
but I am getting sqlcode -199.
Could u please help me in this
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
Nic Clouston
Advanced
Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home
|
| Posted: Mon May 14, 2007 3:16 am Post subject: |
|
|
What do your error messages say? Have you looked up -199? If you did you would undedrstand that perhaps you need to give more than just the error code.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon May 14, 2007 6:55 am Post subject: |
|
|
| Quote: |
but I am getting sqlcode -199.
Could u please help me in this
|
psmadhusudhan,
If you have compared the 2 sql's you would have found the mistake. you are missing a comma before the TABLE keyword (before the outer select). Add the comma and you should be fine
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Mon May 14, 2007 7:43 am Post subject: |
|
|
Kolusu
Thanks for correction. I have run sql after correcting but it is retrieving zero rows. My table consists of 2000 rows.
What might be the problem? I tried to retrieve 50th row. Please help me
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon May 14, 2007 8:25 am Post subject: |
|
|
psmadhusudhan,
Do you have any duplicates for POL_POLICY_REF_S ? Run this sql and check the row #
| Code: |
SELECT POL_POLICY_REF_S
,Row#
FROM NUDW6.TDWDPOL A
TABLE (SELECT COUNT(*)+1 AS ROW#
FROM NUDW6.TDWDPOL B
WHERE B.POL_POLICY_REF_S < A.POL_POLICY_REF_S) AS TEMP_TAB
;
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Tue May 15, 2007 1:14 am Post subject: |
|
|
Kolusu
Yes, I have duplicates for POL_POLICY_REF_S. The maximum value of row# i got is 3353.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Thu May 17, 2007 3:34 am Post subject: |
|
|
Please help me in achieving this. 
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu May 17, 2007 8:10 am Post subject: |
|
|
| Quote: |
Please help me in achieving this
|
psmadhusudhan,
You are missing an important point here. Since your table has duplicates how would you have duplicate rows.
try this sql
| Code: |
SELECT POL_POLICY_REF_S
FROM NUDW6.TDWDPOL A
,TABLE (SELECT COUNT(DISTINCT(POL_POLICY_REF_S)) + 1 AS ROW#
FROM NUDW6.TDWDPOL B
WHERE B.POL_POLICY_REF_S < A.POL_POLICY_REF_S) AS TEMP_TAB
WHERE ROW# = 50
FETCH FIRST 1 ROW ONLY
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Thu May 17, 2007 10:27 pm Post subject: |
|
|
Kolusu
Thanks for your help. Your code is fetching is 50th POL_POLICY_REF_S , but my intention is not that. I want to have the POL_POLICY_REF_S which is on 50th row. Means whatever POL_POLICY_REF_S might be there on 50th row it should be fetched.
And I want to have generic code such that even If I want to fetch complete row with all columns, the code should help me doing it. Means I want to have all the data on that exact row. Please help me in achieving this.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri May 18, 2007 6:40 am Post subject: |
|
|
psmadhusudhan,
| Quote: |
Your code is fetching is 50th POL_POLICY_REF_S , but my intention is not that. I want to have the POL_POLICY_REF_S which is on 50th row. Means whatever POL_POLICY_REF_S might be there on 50th row it should be fetched.
|
what do you mean by the 50th row? DB2 is a relational database system. That means the rows fetched for a query are - related in some way - the conditions in your WHERE clause'
and - are either ordered - ORDER BY clause - or not
DB2 does not guarantee the same 'row order' each time, when there is no ORDER BY clause.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri May 18, 2007 7:52 am Post subject: |
|
|
| psmadhusudhan wrote: | Kolusu
Thanks for your help. Your code is fetching is 50th POL_POLICY_REF_S , but my intention is not that. I want to have the POL_POLICY_REF_S which is on 50th row. Means whatever POL_POLICY_REF_S might be there on 50th row it should be fetched.
And I want to have generic code such that even If I want to fetch complete row with all columns, the code should help me doing it. Means I want to have all the data on that exact row. Please help me in achieving this. |
psmadhusudhan,
Unless your table is defined to have rownum column, there is NO way you can retrieve the 50th row. I rarely ask the reason as to "why" you want to do this? What exactly is the requirement. I am baffled at the whole idea that there are specifications to get the 50th row.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
