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
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...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.