IBM Mainframe Forum

Hi,

I'm facing some issue where in some fields needs to be overwritten in th result set.My requirement is as below

There are two tables tab1 with 10 columns (Col1 is one of the field) and tab2(12 fields with Col1 as one of the field).There are six common fields between these two tables.
1>In the first scenario extract records from Tab1 on some condition witrh all the fields
2>Extract all the records form Tab2 for all fields
3>Need to overlay the six common fields from tab2 in to result when ever there is a match between the above two results(Ie. Six common field values should be taken from Tab2 instead of Tab1 when ever there is a match based on col1 and values from Tab1 table must retain for other accounts when there ism no mismatch)

For example if tab1 has 10 records AND TAB2 has 6 records which are matching with Tab1 and for these six records common field values should come from Tab2 and for remianing four records the same values from should retain.So the output will be of 10 records with some overlaying values.

Please advise me how can i acheive this .(By using QMF/Spoofi/Ezytrieve).
Thanks in advance for your help on this.

Cheers,
Have a Good Day.

Hello and welcome to the forum,

Your request needs clarification.

Post some sample input data from the 2 tables (a key and one data column would be enough) and how the 2 tables might be changed/updated by this process. Also show if there is any output that is not these 2 tables.

Hi,

Thanks for your reply..
Example:

1>Table1 with the following fields
Cm_no
cm_name
dob
address1
address2
address3
state

2>Second table Table2 with the following fields
Cm_no
cm_name
dob
address1
address2

Query1:Get all the card members whose DOB(date of birth is greater tahn 01.01.1980 from Table1
Query2:Get all the card members whose DOB(date of birth is greater tahn 01.01.1980 from Table2
Query3:This gives final output which should have same no.of rows reported in the first query but with common fields(cm_name,dob,address1,address2) should be from second query result.(here the diffficulty is second query will not result same no.of records that that first query gives.So if the card number is there in both the tables the result shd have common fields from table2 and if no match then the values should be from table1.)

Hope this clearly explains the issue.

Thanks in advance for your help on this.

Cheers,
Have a gud day!!

Hello,

You need to post horizontally (rather than vertically) and show multiple rows in each table. Then show the same rows after the process runs how the originial rows might be updated by the process. If some additional output (something not in the 2 input tables) is output that needs to be posted as well.

This will give one view of the 2 input tables. A second view of the tables after the process runs. And any other output this process needs to create.

There does not need to be hi volume input, just enough to demonstrate the rules for your process.

I believe it looks something like this :

update table1 A set (col2,col3,col4) = (select col2,col3,col4 from table2 B where a.col1=b.col1)
where exists (select 1 from Table2 B2 where a.col1=b2.col1)

Don't forget the exists because otherwise col2,col3,col4 will be set to null for those that don't have a corresponding entry in Table2

or if you don't want update but just an output :



the case expression for col4 is in case col4 is nullable in Table2 (ie. if a matching row exists and col4 is null in Table2 then null must be in the output)
for this one must test the non-existance of a matching row on "b.col1 is null"