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#)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
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
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ...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.