Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need to read duplicate rows from table one by one in a loop

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

New User


Joined: 31 Aug 2016
Posts: 72
Location: India

PostPosted: Wed Oct 07, 2020 11:08 pm    Post subject: Need to read duplicate rows from table one by one in a loop
Reply with quote

Hi All,

Can anyone please advise me regarding a scenario for which i need to build the solution.

I have a table like below.

Code:
ColumnA | ColumnB | ColumnC | DATE


In the program, based upon this condition -

Code:
IF DATE > System Date


then run the below query :
Code:
EXEC SQL
SELECT DATE INTO :WS-DATE FROM TABLE
WHERE ColumnA = :some value
            ColumnB = :some value
            ColumnC = :some value


The issue is, in the table ColumnA, ColumnB and ColumnC values can be same. So sometimes it is having more than 1 same rows and at that time program is getting abended with duplicate rows.

I was thinking to write a cursor and fetch the value one by one but the issue is I have to fetch the value of date for each row and do some functionality if the row is present. I have to do this functionality for each row even if it is duplicate and to reach on this para which is having this query, above mentioned condition (IF DATE > System Date) should be satisfied.

So is there any way to read the duplicated row directly when coming on this query for second or third time ?

Please let me know your suggestions. Thanks.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2422
Location: Hampshire, UK

PostPosted: Thu Oct 08, 2020 2:00 am    Post subject: Reply to: Need to read duplicate rows from table one by one in a loop
Reply with quote

This is the situation for which cursors were invented.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2492
Location: NY,USA

PostPosted: Thu Oct 08, 2020 3:07 am    Post subject:
Reply with quote

If you don’t want to fail for duplicate rows then either
1. Handle -811 as a valid SQLCODE and process
2. Or write FETCH FIRST 1 ROW ONLY to this select to get one row always.

I believe the DATE value is same for all duplicated rows in that case you can use either one of the solution and if not then use CURSORS.
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 NOT ABLE TO FIND DB2 ROWS Suehowe DB2 2 Sat Oct 10, 2020 2:05 am
No new posts Read 4MB message and split into multi... myselfsk15 COBOL Programming 9 Fri Sep 25, 2020 7:24 am
No new posts how to read accounting information in... jzhardy COBOL Programming 1 Mon Sep 21, 2020 9:57 am
No new posts Dynamically read input files using BP... Srinivas B COBOL Programming 20 Sat Sep 05, 2020 1:48 pm
No new posts EIBRESP=16 and EIBRESP2=25 while doi... sancraig16 CICS 2 Thu Sep 03, 2020 2:53 am

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us