Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To Select only the latest first record from db2 table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Tue May 31, 2011 7:58 pm    Post subject: To Select only the latest first record from db2 table
Reply with quote

Hi,

Can anyone give me a query to select only the latest record from the db2 table and only one record.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 31, 2011 8:09 pm    Post subject:
Reply with quote

order by ? fetch first ?
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Tue May 31, 2011 8:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 31, 2011 8:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 01, 2011 11:37 am    Post subject: Re: To Select only the latest first record from db2 table
Reply with quote

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
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Wed Jun 01, 2011 2:17 pm    Post subject: Reply to: To Select only the latest first record from db2 ta
Reply with quote

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
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Wed Jun 01, 2011 2:22 pm    Post subject:
Reply with quote

You need to specify FETCH FIRST ROW ONLY after order by clause.
Back to top
View user's profile Send private message
santosh ks

New User


Joined: 27 May 2011
Posts: 16
Location: india

PostPosted: Wed Jun 01, 2011 2:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Wed Jun 01, 2011 2:37 pm    Post subject: Reply to: To Select only the latest first record from db2 ta
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 01, 2011 2:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Wed Jun 01, 2011 4:18 pm    Post subject: Reply to: To Select only the latest first record from db2 ta
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 01, 2011 4:26 pm    Post subject:
Reply with quote

sounds as if you are developing under version 7
and your qmf is version 8.

pays to know your environment.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Wed Jun 01, 2011 4:30 pm    Post subject: Reply to: To Select only the latest first record from db2 ta
Reply with quote

i checked now, both the versions are 7.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 01, 2011 4:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 01, 2011 5:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Jeevankumar.Pochammala

New User


Joined: 04 May 2011
Posts: 7
Location: INDIA

PostPosted: Sun Jun 12, 2011 10:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Jun 13, 2011 1:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us