View previous topic :: View next topic
|
Author |
Message |
kumar1234
New User
Joined: 06 Nov 2007 Posts: 84 Location: bangalore
|
|
|
|
Hi,
Can anyone give me a query to select only the latest record from the db2 table and only one record. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
order by ? fetch first ? |
|
Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011 Posts: 16 Location: india
|
|
|
|
Can you be more specific with your question? How can we give the query without specifying any data.
Before posting it directly here, do google it. You will find many solutions.
Are you so lazy that even this should be done by us.
Anyway you can use a query of this kind
SELECT * FROM table
where col = (SELECT MAX(col) FROM table)
Here 'col' can be one of the column of the table which increments each and every time a new record is added into the table.[/quote] |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
Can anyone give me a query to select only the latest record from the db2 table and only one record. |
Can't be done, there are no records in a db2 table |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
kumar1234 wrote: |
Hi,
Can anyone give me a query to select only the latest record from the db2 table and only one record. |
What do you mean by latest record? Do you have a time stamp column in the table? |
|
Back to top |
|
|
kumar1234
New User
Joined: 06 Nov 2007 Posts: 84 Location: bangalore
|
|
|
|
Hi,
Here is the query that i have coded but this will give sql -811, I want to get only the latest only one record based on the time stamp.
Can you tell me what else do I need to add to this query to fetch only one record.
SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Thanks. |
|
Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010 Posts: 25 Location: Gurgaon
|
|
|
|
You need to specify FETCH FIRST ROW ONLY after order by clause. |
|
Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011 Posts: 16 Location: india
|
|
|
|
This query will fetch multiple rows from the table since there are multiple rows present in the talbe.
You can use this as cursor and fetch only the first record, then it is possible to fetch only one record. |
|
Back to top |
|
|
kumar1234
New User
Joined: 06 Nov 2007 Posts: 84 Location: bangalore
|
|
|
|
Using Cursor is a good option but my customer wants me to fetch without using cursor in a single query in the program. Is there any ways to do this ? |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Modify the query
SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Fetch first 1 row only ; |
|
Back to top |
|
|
kumar1234
New User
Joined: 06 Nov 2007 Posts: 84 Location: bangalore
|
|
|
|
Using this query I compiled but I am getting an error as '"ORDER BY" CLAUSE NOT PERMITTED'.
I removed the host variables and ran in QMF this same query is working fine here. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
sounds as if you are developing under version 7
and your qmf is version 8.
pays to know your environment. |
|
Back to top |
|
|
kumar1234
New User
Joined: 06 Nov 2007 Posts: 84 Location: bangalore
|
|
|
|
i checked now, both the versions are 7. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
i checked now, both the versions are 7. |
doubt that,
possibly qmf comes with pre-release sql but I doubt it. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
if ACTIVITY_TS is unique (at least within DRAFT_OFFICE ,DRAFT_LOGICAL_NO )
SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT A
WHERE DRAFT_OFFICE = :WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = :WS-DRAFT-NUM
and ACTIVITY_TS =
(select max(ACTIVITY_TS) from GIWHS$01.WHS_20_EXTRACT B
where A.DRAFT_OFFICE = B.DRAFT_OFFICE
and a.DRAFT_LOGICAL_NO = b.DRAFT_LOGICAL_NO )
"ORDER BY" CLAUSE NOT PERMITTED probably has something to do with the precompiler version/settings. |
|
Back to top |
|
|
Jeevankumar.Pochammala
New User
Joined: 04 May 2011 Posts: 7 Location: INDIA
|
|
|
|
Hello Kumar1234,
just small modification the query given by gylbharat
SELECT ESCHEAT_IND,ACTIVITY_TS
INTO :WHS-20-ESCHEAT-IND,
:WHS-20-ACTIVITY-TS
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Fetch first 1 row only ;
I those this query work fine |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Jeevankumar.Pochammala wrote: |
Hello Kumar1234,
just small modification the query given by gylbharat
SELECT ESCHEAT_IND,ACTIVITY_TS
INTO :WHS-20-ESCHEAT-IND,
:WHS-20-ACTIVITY-TS
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Fetch first 1 row only ;
I those this query work fine |
Hi Jeevan,
How fetching timestamp in this query will help? |
|
Back to top |
|
|
|