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
 

 

Fetch Unique and sorted data from two Cobol tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Mon Sep 17, 2012 2:35 pm    Post subject: Fetch Unique and sorted data from two Cobol tables
Reply with quote

Hi,

I have two input sorted COBOL tables (Array1 and Array2). I want to fetch the first column of the both the tables without duplicates and in sorted order, extracted in the first column of 3rd Cobol table(Array3).
No condition on rest of the fields of input Cobol tables.

Structure-
Array1(Emp_Id1, Amount1, Access_code1)
Array2(Emp_Id2, Amount2, Access_code2)
Array3((Emp_Id3, Name3, Amount3, Amount4, Amount5)

Emp_Id -- Pic 9(10)

Please see the below example for your reference :-


Array1 --

EMP_ID1
-------------

Code:
1111111111
1111111111
2222222222
4444444444
6666666666


Array2 --

EMP_ID2
-------------

Code:
1111111111
1111111111
2222222222
2222222222
3333333333
5555555555



Array3 :-


EMP_ID3
-------------

Code:
1111111111 
2222222222
3333333333
4444444444
5555555555
6666666666


Please let me know if you have any concern.

Kindly let me know how we can do this in the MF COBOL. I am not sure if there is any way to do it using sort or merge commands?
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Sep 17, 2012 3:09 pm    Post subject:
Reply with quote

  • This sounds like a simple "file-match" kind of exercise - what have you tried?
  • Have you looked at the sticky in COBOL Forum for "2-File Match/Merge sample code". This should give you a idea to get going.
OTOH, First, COBOL has TABLEs not ARRAYs. You've used both of them but ARRAY is not a correct notion when you're in a COBOL Forum. Second, one doesn't 'fetch' values from COBOL Tables nor do they have columns.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Mon Sep 17, 2012 3:35 pm    Post subject:
Reply with quote

Anuj,

Thanks for your response.

Quote:
This sounds like a simple "file-match" kind of exercise - what have you tried?


I have tried the similar logic comparing ONE TO MANY and also, check if the picked value is already populated to final table or not.
But I would wonder if we can do the same activity using Sort or Merge commands to fetch the Unique Emp_Id taken from both the input tables jointly.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Sep 17, 2012 3:49 pm    Post subject:
Reply with quote

deepak_munjal,

yes SORT control statements (merge is a completely different thing)
to input two files and perform the matching and reporting necessary.

there is no need to CALL SORT from a COBOL program to accomplish this,
the fact that you have two COBOL Internal Tables
and want to populate a third,
just means that you have to write the code necessary
to do the same thing that a Stand-alone Sort (with the appropriate control statements)
would do.

regardless of the methodology you decide to implement for your solution,
you will have to restate your requirements.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Mon Sep 17, 2012 3:55 pm    Post subject: Reply to: Fetch Unique and sorted data from two Cobol tables
Reply with quote

From what has been described so far, why not put data in the third table when putting data in either the first or the second table. If duplicate in first, don't attempt. If duplicate in second, don't attempt. If already in third (previously inserted from the "other" table), don't attempt.

Cobol doesn't have a lot of "do magic" "functions", so best not to expect them in the first place. The intrinsic functions that there are are listed in the manuals, link at the top of the page.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Mon Sep 17, 2012 4:54 pm    Post subject:
Reply with quote

Thanks Bill for your sugestion.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Sep 17, 2012 6:57 pm    Post subject:
Reply with quote

Your description about what you want to do and/or doing is confusing:

For example, when you say
Quote:
I have tried the similar logic comparing ONE TO MANY and also, check if the picked value is already populated to final table or not.
To try this - have you written a COBOL program? If yes, what happened? It worked? It did not work?

And by this
Quote:
But I would wonder if we can do the same activity using Sort or Merge commands to fetch the Unique Emp_Id taken from both the input tables jointly.
do you mean to CALL SORT using COBOL program, as Dick indicates? Or you are talking about external SORT, creating a seprate Job for SORT as in PGM=SORT? But then, you are in the COBOL part of the Forum...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Sep 17, 2012 10:01 pm    Post subject:
Reply with quote

Hello,

Quote:
I have two input sorted COBOL tables (Array1 and Array2).
How does this data get into your program? How does the data from Array3 get into your program?

What do you want as output?

If would help of you post all of the fields for the little bit of data for all of the arrays.

From what you've provided, all the only way anyone can reply is to guess - which is not a good way. . . Or ask for more complete info.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Sep 17, 2012 11:24 pm    Post subject: Reply to: Fetch Unique and sorted data from two Cobol tables
Reply with quote

there is one simple change ...
the standard two file match code issues ( when needed ) ONE read at the time for each file

when there are duplicates You will need to keep reading until the key is different,

does not seem overly complicated
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 18, 2012 12:22 am    Post subject:
Reply with quote

Hello,

I'm still not sure i understand if there are 2 inputs or 3 . . .

Maybe TS will clarify.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Tue Sep 18, 2012 2:36 am    Post subject:
Reply with quote

Dick,

Quote:
How does this data get into your program? How does the data from Array3 get into your program?

What do you want as output?


Array1 and Array2 got the data from two seperate DB2 Cursors.
Array3 is required for reporting purpose for which I need unique all Emp Ids from Array1 and Array2.

Please note- Both the tables(Array1 and Array2) could have multiple rows with same Employee Id. And All the Emp_id should get included from Array1 and Array2 to Array3 only once(To get the Unique Email Id list).

Also, Earlier I have completed this using perform loop, no. of If-Else etc.. which seems very difficult to understand due to use of multiple nested If-Else which lead to around 70-80 line of codes.

But now after Bill's suggestion, I have copied all the Emp_Id AS IT IS from Array1 and Array2 to a Temp table TEMP_Array one by one. Hence, Temp table looks Like-


TEMP_Array
---------------

Code:

1111111111
1111111111
2222222222
4444444444
6666666666
1111111111
1111111111
2222222222
2222222222
3333333333
5555555555


And then I have Used the SORT command to sort this.
And then Using simple perform with comparing with the previous entry, I have removed the duplicates and copy it to the Array3.

Array3
---------------

Code:
1111111111 
2222222222
3333333333
4444444444
5555555555
6666666666


I am not sure if you have any better way to do the above task.
Appreciate your support.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Sep 18, 2012 3:00 am    Post subject: Reply to: Fetch Unique and sorted data from two Cobol tables
Reply with quote

Mmmm.... not quite what I suggested.

If you have your two "Cobol tables", which have been loaded with data "en mass", then to get your third "Cobol table" is a variation of the "two-file match", with the "Cobol tables" instead of files.

Starting with the first entry in both tables, copy the lowest key from the two tables to Table 3. Increase subscript/index for table just used - at end of one table, copy remaining entries from other table.

The only extra you need is to deal with duplicates. There are two types you have to deal with, "internal" duplicates in each of the two tables, and duplicates between the two tables.

You might find it simpler to deal with both cases at once, in the paragraph/section you write to add the entry to Table 3. If the new key for Table 3 is the same as the previous key for table 3, do not add it.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 18, 2012 11:40 pm    Post subject:
Reply with quote

Hello,

I believe this is being made more difficult than it needs to be. . .

I do not understand why so many if-else statememts are needed. I believe the only if-else code needed should be the "match" code from the sample code in the "sticky".

Keep in mind that you can ORDER both cursors by the "match keys". There should be no need for the "temp array".

The newly posted data does Not include all of the fields you mention - why not? You know what you have/want - as yet, we do not, so you need to provide it.

I still have no clue as to where the majority of the fields in Array3 come from it these are being generated by the code:
Array3((Emp_Id3, Name3, Amount3, Amount4, Amount5)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Sep 19, 2012 12:24 am    Post subject:
Reply with quote

have you thought of just using one CURSOR with a well written SELECT/WHERE Clause?

the problem requirement has never been well described,
but I would bet, 1 cursor and then process the FETCHed rows
and you probably will not need any COBOL Internal Tables.

This sounds like some concoction from Rookie-Central:

two db2 cursors loading into 2 COBOL Internal Tables
to load yet another COBOL Internal Table........

this is probably a CICS program.........
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm


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