SELECT DISTINCT APPART, APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING'
the result is
Part Seq Number BLAU1003BA 30 BLAU1006AA 30 BLAU1004BA 30 BLAU1002BA 40
but when I try to do both tables with this SELECT AOPART, AOSEQ# FROM CSTDAT/METHDR WHERE METHDR.AOPART IN ( SELECT METHDO.APPART FROM METHDO WHERE APO DES = 'EXTERNAL DRYING ') AND
METHDR.AOSEQ# IN (SELECT METHDO.APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING ') AND AODEPT = '001OS'
I get this:
Part Seq Number BLAU1003BA 30 BLAU1006AA 30 BLAU1004BA 30 BLAU1002BA 30 BLAU1002BA 40
Notice the BLAU1002BA occurs twice. What I believe is happening is that I am getting the part at every possible occurrence of the sequence that my condition exists.
How do I get the unique value and what would be the update statement?
Here's an example of the UPDATE statement that you're trying to run:
UPDATE methdo SET apodes = (SELECT R.apodes FROM methdo O INNER JOIN methdr R O.AOPART = R.AOPART and O.AOSEQ# = R.AOSEQ#) WHERE EXISTS (SELECT 1 FROM methdr R2 methdo.AOPART = R2.AOPART and methdo.AOSEQ# = R2.AOSEQ#)
Dig deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
IBM did not change the default of the reuse deleted files function to YES for physical files in order to minimize the impact on any existing ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.