Need an SQL accessing three files to update a flat file

[TracyV]

I have a flat file i want to update via SQL. I am not very proficient in SQL so I'm not sure how to do this. I have three files I need to access in this statement:

EPLP14 - Flat File
CSB6REP - Landlord File
CSIOREP - Phone Number File


If the landlord (B6KINB) NE *Zeros (CSB6REP file)
link to the flat file i want to update (EPLP14 file) with the landlord field (CSB6REP/B6KINB) to the landlord field on the flat file (EPLP14/A1B2NB)

Then link to the Phone Number File (CSIOREP) to get the appropriate phone numbers to update into the EPLP14 file.

If phone type = 'H' (IOI2ST = 'H') from CSIOREP file
Set EPLP14/CHAREA1 = CSIOREP/IOF7NB
Set EPLP14/CHPHON1 = CSIOREP/IOF8NB

If phone type = 'I' (IOI2ST = 'I') from CSIOREP file
Set EPLP14/CHAREA2 = CSIOREP/IOF7NB
Set EPLP14/CHPHON2 = CSIOREP/IOF8NB

If phone type = 'C' (IOI2ST = 'C') from CSIOREP file
Set EPLP14/CCAREA1 = CSIOREP/IOF7NB
Set EPLP14/CCPHON1 = CSIOREP/IOF8NB

If phone type = 'D' (IOI2ST = 'D') from CSIOREP file
Set EPLP14/CCAREA2 = CSIOREP/IOF7NB
Set EPLP14/CCPHON2 = CSIOREP/IOF8NB

The SQL statement will then run via a CL program. I do not have the option to do this update with a program. It needs to be SQL. I do have a working example that does something like this update using two files, but i'm not sure as to how/where to add the third file for the above SQL. Here is my working example:

UPDATE tlvlib/eplp14 A SET A.tharea1 = (SELECT i8zjn1 FROM csi8rep B WHERE A.q0KGNB=B.i8KGNB and A.q0aycd=B.i8aycd and B.i8vhst='H'), A.thphon1=(SELECT i8zkn1 FROM csi8rep B WHERE A.q0KGNB = B.i8KGNB and A.q0aycd = B.i8aycd and B.i8vhst='H')WHERE exists(SELECT * FROM csi8rep where A.q0kgnb=i8kgnb and A.q0aycd=i8aycd and i8vhst='H');

Any help would be greately appreciated!

 

[Mercury2]

Here's a clarified example of your "working example" using the scalar function IFNULL() to avoid this EXISTS predicate. Whenever the WHERE predicate doesn't match the required search condition, both fields A.tharea1 and A.thphon1 are updated with blanks. Give it a try if you can.

UPDATE tlvlib/eplp14 A 
SET ( A.tharea1, A.thphon1 )  = 
IFNULL(( SELECT i8zjn1, i8zjn1 FROM csi8rep 
WHERE           i8kgnb = A.q0kgnb  
AND             i8aycd = A.q0aycd  
AND             i8vhst = 'H' ), ' ' )

This might inspire you to build the three files statement

 

[TracyV]

I don't see where the CSB6REP - Landlord File is accessed. The statement should only be performed if the landlord number on the CSB6REP is not equal to zero. Then using that landlord number, link to the phone number file to get the phone numbers. From there, link to the flat file and update the phone numbers.

UPDATE tlvlib/eplp14 A
SET ( A.tharea1, A.thphon1 ) =
IFNULL(( SELECT i8zjn1, i8zjn1 FROM csi8rep
WHERE i8kgnb = A.q0kgnb
AND i8aycd = A.q0aycd
AND i8vhst = 'H' ), ' ' )
Token IFNULL was not valid. Valid tokens: (.

 

[Mercury2]

Try COALESCE instead of IFNULL.

 

[TracyV]

I'm still missing the CSB6REP file which is where I get the landlord number to get to the account (CSI8REP) file.

UPDATE tlvlib/eplp14 A
SET ( A.tharea1, A.thphon1 ) =
(( SELECT coalesce i8zjn1, i8zjn1 FROM csi8rep
WHERE i8kgnb = A.q0kgnb
AND i8aycd = A.q0aycd
AND i8vhst = 'H' ), ' ' )