| View previous topic :: View next topic |
| Author |
Message |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
 Posted: Mon Apr 26, 2004 9:27 am Post subject: SQL Query |
 |
|
| Code: |
col1------------col2-----Col3---col4------col5---col6-----col7
260071311------ FR------ N------3AA2------2------3AA------27.02.2004
260071311------ FR------ N------3AA2------2------3AA------03.12.2003
261587935------ FR------ N------4A 1------1------4A-------22.04.2004
261587935------ FR------ N------4A 1------1------4A-------21.04.2004
263267841------ FR------ N------A 1------1------A--------01.04.2004
263267841------ FR------ N------A 1------1------A--------26.03.2004
|
Required O/P:
I want to select the whole row with second highest date .
How can I do this...
| Code: |
260071311------ FR------ N------3AA2------2------3AA------03.12.2003
261587935------ FR------ N------4A 1------1------4A-------21.04.2004
263267841------ FR------ N------A 1------1------A--------26.03.2004
|
|
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 26, 2004 9:46 am Post subject: |
|
|
Paneendra V K,
Which column in the above data depicts the rate?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Apr 26, 2004 9:47 am Post subject: |
|
|
| 7th cloumn Kolusu. |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Apr 26, 2004 9:50 am Post subject: |
|
|
Actually there are so many rows present in the table.
I am using Cursor to do this.
Could you please give me the syntax using Cursor .......... |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 26, 2004 9:50 am Post subject: |
|
|
Paneedra vk,
7th column looks like a date column. What is the definition of the column? Is it defined as date? And the format also looks different . Is there an embedded decimal point in the data?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Apr 26, 2004 9:55 am Post subject: |
|
|
Name Coltype Length
CRH_ALG_RUN_DT DATE 4 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 26, 2004 10:01 am Post subject: |
|
|
Vkphani,
The date field length is 4? How is that possible? Any column which is defined as date is 10 bytes in length. Can you clarify?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Apr 26, 2004 10:03 am Post subject: |
|
|
Sorry,
The length is 10. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 26, 2004 10:37 am Post subject: |
|
|
Vkphani,
The simlest way to do this is adding an order by clause with select. Since you are using a cursor to fetch the rows we can filter the rows
| Code: |
W-STORE-KEY PIC X(10) VALUE SPACES.
EXEC SQL
DECLARE PHANI_CSR CURSOR FOR
SELECT * FROM PHANI
ORDER BY COL1,COL7 DESC
END-EXEC
PROCEDURE DIVISION.
EXEC SQL
OPEN PHANI_CSR
END-EXEC
EXEC SQL
FETCH PHANI_CSR
INTO :TABLE.COL1
,:TABLE.COL2
,:TABLE.COL3
,:TABLE.COL4
,:TABLE.COL5
,:TABLE.COL6
,:TABLE.COL7
END-EXEC
EVALUATE SQLCODE
WHEN +0
IF W-STORE-KEY = COL1 OF TABLE
CONTINUE
ELSE
PERFORM FETCH
W-STORE-KEY = COL1 OF TABLE
END-IF
WHEN +100
CLOSE THE CURSOR
END-EVALUATE
|
Hope this helps...
Cheers
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
