Solved

SQL with subquery

Posted on 2013-06-03
39
614 Views
Last Modified: 2013-06-11
I have the following tables:
Table:  CJOBDR
Fields:
edjob# - relates to CJOBH.dnjob
edseq# - relates to METHDR.aoseq#
edlin# - relates to METHDR.aolin#
edsetp
edruns

Table:  CJOBH
Fields:
dnjob
dnpart - relates to METHDR.aopart

Table:  METHDR
Fields:
aopart
aoseq#
aolin#
aosetp
aoruns


See the attached .xls file for what I am trying to accomplish.

I need to link CJOBDR.EDJOB# to CJOBH.DNJOB and then with that, using the
CJOBH.DNPART = METHDR.AOPART and
CJOBDR.EDSEQ# = METHDR.AOSEQ# and
CJOBDR.EDLIN# = METHDR.AOLIN#

I need in your solution:
1.  The select statement to do this
2.  An update statement to make
CJOBDR.EDSETP = METHDR.AOSETP
CJOBDR.EDRUNS = METHDR.AORUNS
Query.xls
0
Comment
Question by:ndornack
  • 20
  • 6
  • 6
  • +3
39 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39217095
Hi ndornack,

This is the same question that you asked a few minutes ago in the DB2 forum and here.  The original forum selection was appropriate.

Before assistance can be given, you need to confirm whether this is work related or a class / educational assignment.


Thanks,
Kent
0
 

Author Comment

by:ndornack
ID: 39217099
work
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39217168
Have you tried top build the SQL?

General will be

SELECT  List of fields
FROM CJOBDR INNER JOIN CJOBH ON link fields
   INNER JOIN METHDR ON link fields

mlmcc
0
 

Author Comment

by:ndornack
ID: 39217190
Ok - I have the select working.  Now I just need help with the update.  If I have this select, how would I update the fields:  CJOBDR.EDSETP = METHDR.AOSETP AND CJOBDR.EDRUNS = METHDR.AORUNS

* * * * * *

SELECT JOB.edjob#, SUB.DNPART, JOB.EDSEQ#, JOB.EDLIN#, JOB.EDOPNM JOBOPNUM, SUB.AOOPNM RTEOPNUM, JOB.EDSETP JOBSETUP, SUB.AOSETP RTESETUP, JOB.EDRUNS JOBRUN, SUB.AORUNS RTERUN
FROM cmsdat.cjobdr JOB
LEFT OUTER JOIN
(select DISTINCT JOBH.dnjob, JOBH.dnpart, ROUTE.AOOPNM, ROUTE.AOSETP, ROUTE.AORUNS
from cmsdat.cjobh JOBH
left outer join cmsdat.methdr ROUTE on
JOBH.dnpart = ROUTE.aopart) SUB  
ON (JOB.EDJOB# = SUB.DNJOB AND
JOB.EDOPNM = SUB.AOOPNM)
WHERE JOB.EDRTYP = 'A' AND
JOB.EDSTAT = 'N'
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39217546
What is your RDBMS? DB2?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 39217570
which version of db2 are you running?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39217579
Can you try this?
MERGE INTO cmsdat.cjobdr AS JOB 
USING (SELECT DISTINCT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS 
         FROM cmsdat.cjobh JOBH 
              LEFT OUTER JOIN cmsdat.methdr ROUTE 
                           ON JOBH.dnpart = ROUTE.aopart) AS SUB 
ON ( JOB.EDJOB# = SUB.DNJOB 
     AND JOB.EDOPNM = SUB.AOOPNM ) 
WHEN MATCHED THEN 
  UPDATE SET JOB.EDSETP = SUB.AOSETP, 
             JOB.EDRUNS = SUB.AORUNS;

Open in new window

0
 

Author Comment

by:ndornack
ID: 39222160
Sharath_123 - thanks for your reply.

I am trying your suggestion using my test database TEST0313 and a specific job 'X003506314'

This is my statement:
MERGE INTO TEST0313.cjobdr AS JOB
USING (SELECT DISTINCT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS
         FROM TEST0313.cjobh JOBH
              LEFT OUTER JOIN TEST0313.methdr ROUTE
                           ON JOBH.dnpart = ROUTE.aopart
              WHERE JOBH.DNJOB = 'X003506314') AS SUB
ON ( JOB.EDJOB# = SUB.DNJOB
     AND JOB.EDOPNM = SUB.AOOPNM)
WHEN MATCHED THEN
  UPDATE SET JOB.EDSETP = SUB.AOSETP,
             JOB.EDRUNS = SUB.AORUNS;

I am getting the error:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token MERGE was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. Cause . . . . . :   A syntax error was detected at token MERGE.  Token MERGE is not a valid token.  A partial list of valid tokens is ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token MERGE. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Processing ended because the highlighted statement did not complete successfully
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39222289
Hi ndornack,

Either you're using an older version of DB2 that doesn't yet support the MERGE statement, or the statement in your procedure immediately prior to the MERGE statement isn't terminated.

Check the the token immediately before the MERGE statement is a semi-colon.  (The easy fix.)

What version of DB2 are you running?



Kent
0
 

Author Comment

by:ndornack
ID: 39222343
We are running on the iSeries with AS400.  I believe its using DB2 UDB

I believe we need to use the COALESCE statement.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39222363
What version of DB2 are you running?

COALESCE may be appropriate at some place in the procedure, but it's not the cause of the error that you're seeing.


Kent
0
 

Author Comment

by:ndornack
ID: 39222417
I am contacting our vendor to find out what version.  I am running the query through the System i Navigator, using the DB2 Query Manager.  It says this is release V5R4M0
0
 

Author Comment

by:ndornack
ID: 39222431
DB2 for i5/OS: V5R4
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39222449
Hi ndornack,

We'll have to find a different solution.  The MERGE statement isn't available until version 6.


Kent
0
 

Author Comment

by:ndornack
ID: 39222510
0
 

Author Comment

by:ndornack
ID: 39222538
I don't want to have to write a separate statement for each field I want to update.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39222593
DB2 for iSeries is slightly different that IBM's other DB2 implementations.

That said, I think that this will work.  Give it a try (in a test environment).


Kent


UPDATE job
  SET (JOB.EDSETP, JOB.EDRUNS) = (SUB.AORUNS, SUB.AORUNS)
FROM TEST0313.cjobdr job
INNER JOIN
(
  SELECT DISTINCT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS
  FROM TEST0313.cjobh JOBH
  LEFT OUTER JOIN TEST0313.methdr ROUTE
    ON JOBH.dnpart = ROUTE.aopart
  WHERE JOBH.DNJOB = 'X003506314'
) AS SUB
  ON JOB.EDJOB# = SUB.DNJOB
 AND JOB.EDOPNM = SUB.AOOPNM;
0
 

Author Comment

by:ndornack
ID: 39222634
Not working:
UPDATE job
  SET (JOB.EDSETP, JOB.EDRUNS) = (SUB.AOSETP, SUB.AORUNS)
FROM TEST0313.cjobdr job
INNER JOIN
(
  SELECT DISTINCT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS
  FROM TEST0313.cjobh JOBH
  LEFT OUTER JOIN TEST0313.methdr ROUTE
    ON JOBH.dnpart = ROUTE.aopart
  WHERE JOBH.DNJOB = 'X003506314'
) AS SUB
  ON JOB.EDJOB# = SUB.DNJOB
 AND JOB.EDOPNM = SUB.AOOPNM;

Receiving the error:
SQL State: 42703
Vendor Code: -5001
Message: [SQL5001] Column qualifier or table SUB undefined. Cause . . . . . :   Name SUB was used to qualify a column name or was specified as the operand of the RRN, HASHED_VALUE, PARTITION, NODENAME, NODENUMBER, DBPARTITIONNAME, DBPARTITIONNUM, DATAPARTITIONNAME, or DATAPARTITIONNUM scalar function.  The name is not defined to be a table designator in this SQL statement or the table designator cannot be referenced where it is specified in the SQL statement. If a correlation name is specified following the table name in a FROM clause, the correlation name is considered to be the table designator.  If a correlation name is not specified, the table name is considered to be the table designator.  If using SQL naming and the table is qualified with authorization name, then the table designator is authorization-name.table-name.  If the authorization name is not specified, the table designator is the implicit authorization name followed by the table name. Correlation from a nested table expression to a higher level table is only allowed if the TABLE keyword is used for the definition of the nested table expression. If the name is *N, a lateral correlation reference from a nested table expression is not allowed.  You can not correlate to a table at a higher level than the nested table expression for one of the following reasons: -- The nested table expression contains a UNION, EXCEPT, or INTERSECT. -- The nested table expression uses the DISTINCT keyword in the SELECT clause. -- The nested table expression contains an ORDER BY clause. -- The correlated provider is in the same FROM clause as the nested table expression but is part of a RIGHT OUTER JOIN or RIGHT EXCEPTION JOIN. -- The nested table expression is in the FROM clause of another nested table expression that contains one of these restrictions. In an OLAP function, the ORDER OF table designator must refer to a table designator in the FROM clause of the subselect. Recovery  . . . :   Ensure all column names are qualified with a valid table designator.  Make sure a table designator is specified as the argument to the function.  Use the TABLE keyword to allow correlated columns within a nested table expression. Try the request again.

Processing ended because the highlighted statement did not complete successfully
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Expert Comment

by:tliotta
ID: 39224772
Instead of [ ) AS SUB ], try simply [ ) SUB ]. I don't think the "AS" should be there.

Tom
0
 

Author Comment

by:ndornack
ID: 39225520
Tried running:
 UPDATE job
  SET (JOB.EDSETP, JOB.EDRUNS) = (SUB.AOSETP, SUB.AORUNS)
FROM TEST0604.cjobdr job
INNER JOIN
(
  SELECT DISTINCT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS
  FROM TEST0604.cjobh JOBH
  LEFT OUTER JOIN TEST0604.methdr ROUTE
    ON JOBH.dnpart = ROUTE.aopart
  WHERE JOBH.DNJOB = 'X003506314'
) SUB
  ON JOB.EDJOB# = SUB.DNJOB
 AND JOB.EDOPNM = SUB.AOOPNM;

And its still erroring out on this line  (DOES NOT LIKE THE USE OF 'SUB' AS A TABLE NAME)
SET (JOB.EDSETP, JOB.EDRUNS) = (SUB.AOSETP, SUB.AORUNS)

With the error:
Message: [SQL5001] Column qualifier or table SUB undefined. Cause . . . . . :   Name SUB was used to qualify a column name or was specified as the operand of the RRN, HASHED_VALUE, PARTITION, NODENAME, NODENUMBER, DBPARTITIONNAME, DBPARTITIONNUM, DATAPARTITIONNAME, or DATAPARTITIONNUM scalar function.  The name is not defined to be a table designator in this SQL statement or the table designator cannot be referenced where it is specified in the SQL statement. If a correlation name is specified following the table name in a FROM clause, the correlation name is considered to be the table designator.  If a correlation name is not specified, the table name is considered to be the table designator.  If using SQL naming and the table is qualified with authorization name, then the table designator is authorization-name.table-name.  If the authorization name is not specified, the table designator is the implicit authorization name followed by the table name. Correlation from a nested table expression to a higher level table is only allowed if the TABLE keyword is used for the definition of the nested table expression. If the name is *N, a lateral correlation reference from a nested table expression is not allowed.  You can not correlate to a table at a higher level than the nested table expression for one of the following reasons: -- The nested table expression contains a UNION, EXCEPT, or INTERSECT. -- The nested table expression uses the DISTINCT keyword in the SELECT clause. -- The nested table expression contains an ORDER BY clause. -- The correlated provider is in the same FROM clause as the nested table expression but is part of a RIGHT OUTER JOIN or RIGHT EXCEPTION JOIN. -- The nested table expression is in the FROM clause of another nested table expression that contains one of these restrictions. In an OLAP function, the ORDER OF table designator must refer to a table designator in the FROM clause of the subselect. Recovery  . . . :   Ensure all column names are qualified with a valid table designator.  Make sure a table designator is specified as the argument to the function.  Use the TABLE keyword to allow correlated columns within a nested table expression. Try the request again.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39225591
Hi ndornack,

Tom (tliotta) is one our best AS/400 guys.  I'm going to defer to anything that he suggests.

In the meantime, try changing the style of the SET clause from:

  SET (JOB.EDSETP, JOB.EDRUNS) = (SUB.AOSETP, SUB.AORUNS)

to

  SET JOB.EDSETP = SUB.AOSETP, JOB.EDRUNS = SUB.AORUNS

I know that the other versions of DB2 prefer the first style, but the AS/400 is a different animal.


Kent
0
 

Author Comment

by:ndornack
ID: 39225629
Thanks Kent,

I've actually already tried:
SET JOB.EDSETP = SUB.AOSETP, JOB.EDRUNS = SUB.AORUNS

and I get the exact same error.  It doesn't like using the table "SUB"

I'm starting to think I will need to take this query and repeat it for the for each field I want to update.  Seems very redundant, but maybe this is the case.
0
 

Author Comment

by:ndornack
ID: 39225709
Do you think the WHERE EXISTS clause would work here and how would the statement look?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39228016
According to the error message:

You can not correlate to a table at a higher level than the nested table expression for one of the following reasons:
 -- The nested table expression contains a UNION, EXCEPT, or INTERSECT.
 -- The nested table expression uses the DISTINCT keyword in the SELECT clause.
 -- Etc.

Is DISTINCT required for what you need? I haven't seen the form that you're using, so I haven't seen that specific requirement before nor tried to understand the underlying DBMS logic.

Tom
0
 

Author Comment

by:ndornack
ID: 39234404
I removed DISTINCT from the statement and am still getting the same error.

This is what I am running:
 UPDATE job
  SET JOB.EDSETP = SUB.AOSETP,
        JOB.EDRUNS = SUB.AORUNS
FROM TEST0604.cjobdr job
INNER JOIN
(
  SELECT JOBH.dnjob,JOBH.dnpart,ROUTE.AOOPNM,ROUTE.AOSETP,ROUTE.AORUNS
  FROM TEST0604.cjobh JOBH
  LEFT OUTER JOIN TEST0604.methdr ROUTE
    ON JOBH.dnpart = ROUTE.aopart
  WHERE JOBH.DNJOB = 'X003506314'
) SUB
  ON JOB.EDJOB# = SUB.DNJOB
 AND JOB.EDOPNM = SUB.AOOPNM;
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39235803
Can you give a try?
UPDATE (SELECT JOB.EDSETP, 
               SUB.AOSETP, 
               JOB.EDRUNS, 
               SUB.AORUNS 
        FROM   TEST0604.cjobdr job 
               INNER JOIN (SELECT JOBH.dnjob, 
                                  JOBH.dnpart, 
                                  ROUTE.AOOPNM, 
                                  ROUTE.AOSETP, 
                                  ROUTE.AORUNS 
                           FROM   TEST0604.cjobh JOBH 
                                  LEFT OUTER JOIN TEST0604.methdr ROUTE 
                                               ON JOBH.dnpart = ROUTE.aopart 
                           WHERE  JOBH.DNJOB = 'X003506314') SUB 
                       ON JOB.EDJOB# = SUB.DNJOB 
                          AND JOB.EDOPNM = SUB.AOOPNM) AS T1(EDSETP, AOSETP, EDRUNS, AORUNS) 
SET    EDSETP = AOSETP, 
       EDRUNS = AORUNS;

Open in new window

0
 

Author Comment

by:ndornack
ID: 39236009
Sharath_123
Error with this line:
UPDATE (SELECT JOB.EDSETP,

This is the error:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ( was not valid. Valid tokens: <IDENTIFIER>. Cause . . . . . :   A syntax error was detected at token (.  Token ( is not a valid token.  A partial list of valid tokens is <IDENTIFIER>.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token (. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
0
 

Author Comment

by:ndornack
ID: 39236184
This is an alternative statement I am trying, but am getting the error that the SET statement contains more than one row.

UPDATE TEST0604.CJOBDR RCVR
SET
RCVR.EDSETP =
  (SELECT R.AOSETP FROM TEST0604.CJOBDR J
   INNER JOIN QGPL.XMETHDRJ R ON
   J.EDJOB# = R.DNJOB AND
   J.EDOPNM = R.AOOPNM),
RCVR.EDRUNS =
  (SELECT R.AORUNS FROM TEST0604.CJOBDR J
   INNER JOIN QGPL.XMETHDRJ R ON
   J.EDJOB# = R.DNJOB AND
   J.EDOPNM = R.AOOPNM)
WHERE EXISTS
(SELECT 1 FROM QGPL.XMETHDRJ R2
WHERE
RCVR.EDJOB# = R2.DNJOB AND
RCVR.EDOPNM = R2.AOOPNM) AND
RCVR.EDRTYP = 'A' AND
RCVR.EDSTAT = 'N'

*****
Message: [SQL0811] Result of SELECT more than one row. Cause . . . . . :   The result table of a SELECT INTO statement, a subquery, or a subselect of a SET statement contains more than one row.  The error type is 2. If the error type is 1 then a SELECT INTO statement attempted to return more than one row.  If the error type is 2 then a subselect of a basic predicate has produced more than one row.  Only one row is allowed. Recovery  . . . :   Change the selection so that only one result row is returned and then try the request again.  The DECLARE CURSOR, OPEN, and FETCH statements must be used to process more than one result row.  For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row.  If one row was expected, there may be data errors, such as duplicate rows, that are causing more than one row to be returned.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39236443
If you are using iSeries Navigator (iNav), can you use it to retrieve the related table definitions and post them here? I can create the same object definitions on one of my systems to look closer at the desired statement. I don't have V5R4, but I have V5R3 and 6.1 and might get useful info by seeing what happens on those.

It's not clear why you would get the "same" error, i.e., SQL5001.

Can you tell what cume PTF level and DB2 group PTF level are applied to the server? The WRKGRPPTF command will list levels. (You might need to ask an administrator.)

Tom
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39236467
What about this?
UPDATE CMSDAT.CJOBDR AS JOB 
   SET JOB.EDSETP = (SELECT DISTINCT ROUTE.AOSETP 
                       FROM cmsdat.CJOBH JOBH 
                            LEFT OUTER JOIN cmsdat.METHDR ROUTE 
                                         ON JOBH.DNPART = ROUTE.AOPART 
                      WHERE JOB.EDJOB# = JOBH.DNJOB 
                        AND JOB.EDOPNM = ROUTE.AOOPNM), 
       JOB.EDRUNS = (SELECT DISTINCT ROUTE.AORUNS 
                       FROM cmsdat.CJOBH JOBH 
                            LEFT OUTER JOIN cmsdat.METHDR ROUTE 
                                         ON JOBH.DNPART = ROUTE.AOPART 
                      WHERE JOB.EDJOB# = JOBH.DNJOB 
                        AND JOB.EDOPNM = ROUTE.AOOPNM) 

Open in new window

0
 

Author Comment

by:ndornack
ID: 39236650
Sharath_123 - I think your solution is getting close.  Now I have a null error. Can I use COALESCE to resolve this?

Statement I have:
UPDATE TEST0604.CJOBDR AS JOB
   SET JOB.EDSETP = (SELECT DISTINCT ROUTE.AOSETP
                       FROM TEST0604.CJOBH JOBH
                            LEFT OUTER JOIN TEST0604.METHDR ROUTE
                                         ON JOBH.DNPART = ROUTE.AOPART
                      WHERE JOB.EDJOB# = JOBH.DNJOB
                        AND JOB.EDOPNM = ROUTE.AOOPNM),
       JOB.EDRUNS = (SELECT DISTINCT ROUTE.AORUNS
                       FROM TEST0604.CJOBH JOBH
                            LEFT OUTER JOIN TEST0604.METHDR ROUTE
                                         ON JOBH.DNPART = ROUTE.AOPART
                      WHERE JOB.EDJOB# = JOBH.DNJOB
                        AND JOB.EDOPNM = ROUTE.AOOPNM)
WHERE
JOB.EDOPNM <> '' AND
JOB.EDRTYP = 'A' AND
JOB.EDSTAT = 'N'

*****Error:*****
Message: [SQL0407] Null values not allowed in column or variable EDSETP. Cause . . . . . :   One of the following has occurred: -- Column EDSETP is a target column in an UPDATE or INSERT statement for table CJOBDR in TEST0604. Either a null value was specified to be inserted or updated into this column or a value for the column was not specified in an INSERT statement and the column does not allow null values. The null value was specified in the relative entry number 1 in the VALUES list, select list, or SET clause. -- Column EDSETP is specified in an ALTER statement for table CJOBDR in TEST0604.  The attribute of column EDSETP can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable EDSETP is a target variable in an SQL procedure, function, or trigger.  A null value was specified to be set into this variable using a SET or VALUES statement, but the variable does not allow null values.  The null value was specified in relative entry number 1 in the SET or VALUES INTO clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL. If it is a host variable or column then the name is *N. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the NOT NULL attribute of the column specified on the CREATE or ALTER of the column. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the partitioning key attributes of the column specified for the base table on the CREATE TABLE or ALTER TABLE statement. Recovery  . . . :   If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value. Otherwise, change the value so the result is not null.  If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero.  Try the request again.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39236789
>> Can I use COALESCE to resolve this?

Yes.  Try COALESCE(ROUTE.AOSETP,'') Or any other default value you want.

COALESCE(ROUTE.AOSETP,'Default Value')
0
 

Author Comment

by:ndornack
ID: 39237704
I got it working and below is the working statement:  

There were multiple seq# for a opnm and that was causing the more than 1 row error.  I ended up with this, but I think I could have accomplished the same thing with one of your solutions as well.

UPDATE TEST0604.CJOBDR JOB
SET
(JOB.EDSETP, JOB.EDRUNS) =
(SELECT DISTINCT ROUTE.AOSETP, ROUTE.AORUNS
                       FROM TEST0604.CJOBH JOBH
                            LEFT OUTER JOIN TEST0604.METHDR ROUTE
                                         ON JOBH.DNPART = ROUTE.AOPART
                      WHERE JOB.EDJOB# = JOBH.DNJOB
                        AND JOB.EDOPNM = ROUTE.AOOPNM
                        AND JOB.EDSEQ# = ROUTE.AOSEQ#
                        AND ROUTE.AOOPNM <> '')
WHERE EXISTS
(SELECT *
FROM TEST0604.CJOBH JOBH
                            LEFT OUTER JOIN TEST0604.METHDR ROUTE
                                         ON JOBH.DNPART = ROUTE.AOPART
                      WHERE JOB.EDJOB# = JOBH.DNJOB
                        AND JOB.EDOPNM = ROUTE.AOOPNM
                        AND JOB.EDSEQ# = ROUTE.AOSEQ#
                        AND ROUTE.AOOPNM <> '') AND
JOB.EDOPNM <> '' AND
JOB.EDRTYP = 'A' AND
JOB.EDSTAT = 'N'
0
 

Author Comment

by:ndornack
ID: 39237971
I've requested that this question be closed as follows:

Accepted answer: 0 points for ndornack's comment #a39237704

for the following reason:

This is the working solution.
0
 

Author Comment

by:ndornack
ID: 39238002
Kdo,
I agree and will give him credit.
0
 

Author Comment

by:ndornack
ID: 39239998
I've requested that this question be closed as follows:

Accepted answer: 0 points for ndornack's comment #a39237704
Assisted answer: 250 points for Sharath_123's comment #a39236467

for the following reason:

Thanks to all for helping.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39240170
Thanks kdo for the comments.

ndornack, Thanks for closing the question in a proper way.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now