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
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
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