Pete Rawlings 31.Jul.15 06:40 AM a Web browser LEI All Releases All Platforms
I have a database I want to replicate distinct records with. Why not use direct transfer I hear you ask. Well because of how much data it changes. So my dream is to use this example query in the replication environment
SELECT DISTINCT XXX.TABLE1.IMT, XXX.TABLE1.IMOD, XXX.TABLE2.PMAKENAM,
XXX.TABLE2.PMAKEDES, XXX.TABLE2.PMAKEFEA, XXX.TABLE2.PDEVCAT
FROM XXX.ORBTIPRO, XXX.ORBTOEM
WHERE XXX.TABLE1.IPCN = XXX.TABLE2.PCN
AND SUBSTR(XXX.TABLE1.IMT, 1, 1) BETWEEN '0' AND '9'
AND SUBSTR(XXX.TABLE1.IMT, 2, 1) BETWEEN '0' AND '9'
AND SUBSTR(XXX.TABLE1.IMT, 3, 1) BETWEEN '0' AND '9'
AND SUBSTR(XXX.TABLE1.IMT, 4, 1) BETWEEN '0' AND '9'
AND XXX.TABLE1.IMT <> ' ' AND XXX.TABLE1.IMOD <> ' '
AND XXX.TABLE2.PMAKENAM NOT LIKE ' FRED%'
AND XXX.TABLE1.IPRODST IN ('S', 'T', 'G')
ORDER BY XXX.TABLE1.IMT ASC, XXX.TABLE1.IMOD ASC
So the challenge first is the Join , got around this using a Dependant Activity to go get the additional data based on the key.
The issues I now have is the DISTINCT and the NOT LIKE (as I dont have the joined data to use for Conditional clause as Query now split).
I do have this code working in a Direct Transfer so if worse come to worse I could continue or maybe the best thing to do is get the DB2 folks to provide me a view.