IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need to read duplicate rows from table one by one in a loop


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Oct 07, 2020 11:08 pm
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: 2455
Location: Hampshire, UK

PostPosted: Thu Oct 08, 2020 2:00 am
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: 2790
Location: NYC,USA

PostPosted: Thu Oct 08, 2020 3:07 am
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
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Thu Nov 05, 2020 8:51 pm
Reply with quote

Rohit Umarjikar wrote:
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.


Thanks for the suggestions Rohit. appreciate it.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts create table with more than 32k recor... DB2 1
This topic is locked: you cannot edit posts or make replies. COBOL-Common routine that contains al... COBOL Programming 4
No new posts Read two files CLIST & REXX 4
No new posts Create table from List CLIST & REXX 8
No new posts How to read IPv6 from ADDR_INFO in EZ... All Other Mainframe Topics 0
Search our Forums:

Back to Top