Hi. I am a TSQL user trying to make sense of DB2 SQL on the IBMi. I need to populate a field with a percentage of a cost in another field, within the same table. That would be simple enough but I need to filter on field values in 2 separate tables. I think my problem is understanding the joins in DB2.. I put this together but I'm leery of running this code on my IBMi, even over test data.
SET colt = smat * .25 WHERE exists(
FROM wklibj.itmrvbx831 a
JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr
JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr
WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'
or c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')
This looks like it should run but examples I'm finding online lead me to believe that I need to repeat the filters after my enclosed select statement. I also looked at using a merge statement but not sure it's a good fit here..? thanks.