SQL Errr

pts.
Tags:
AS/400
DB2 Universal Database
Hi I am a novice to SQL and I was hoping someone can tell me where I'm going wrong. I have a small sql statement that keeps giving me an error message. Can any one help please. Thank you in advance. Dawn > UPDATE AMFLIBT.MOROUT SET OPSTC = 40 WHERE (select AMFLIBT.MOROUT.ORDNO, AMFLIBT.MOROUT.OPSEQ, AMFLIBT.MOROUT.OPSTC, amflibt.morout.wkctr, amflibt.momast.ostat, AMFLIBT.MOMAST.FITEM FROM AMFLIBT.MOROUT INNER JOIN AMFLIBT.MOMAST ON AMFLIBT.MOROUT.ORDNO = AMFLIBT.MOMAST.ORDNO where fitem = '300.605.407.8' and opseq < '0047' and opstc = 10 and wkctr = 'INFO' and ostat <> 10) SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !< !> != >= ?< ?> ?= IN. Cause . . . . . : A syntax error was detected at token <END-OF-STATEMENT>. Token <END-OF-STATEMENT> is not a valid token. A partial list of valid tokens is < > = <> <= !< !> != >= ?< ?> ?= IN. 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 <END-OF-STATEMENT>. 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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Do it this way:

UPDATE amfLibT.morOut a
SET opsTC = 40
WHERE opSeq<’0047′ AND
opsTC=10 AND
wkCtr=’INFO’ AND
EXISTS(
SELECT 1
FROM amfLibT.momAST b
WHERE a.ordNo=b.ordNo AND
b.fItem=’300.605.407.8′ AND
b.oStat<>10)

In some SQLs, instead of (for instance) “morOut a”, you need to code “morOut AS a”. The “1″ is just a place-holder. It doesn’t matter what goes in that spot.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Sheldon supplied a possible correct answer. It doesn't explain what was wrong with the original though. Here's a more readable presentation of the problem statement:
    UPDATE AMFLIBT.MOROUT SET OPSTC = 40
     WHERE (select AMFLIBT.MOROUT.ORDNO,
                   AMFLIBT.MOROUT.OPSEQ,
                   AMFLIBT.MOROUT.OPSTC,
                   amflibt.morout.wkctr,
                   amflibt.momast.ostat,
                   AMFLIBT.MOMAST.FITEM
                FROM AMFLIBT.MOROUT INNER JOIN AMFLIBT.MOMAST
                ON AMFLIBT.MOROUT.ORDNO = AMFLIBT.MOMAST.ORDNO
                where fitem = '300.605.407.8'
                   and opseq < '0047'
                   and opstc = 10
                   and wkctr = 'INFO'
                   and ostat <> 10
           )
    That should show how the subselect in the WHERE clause is related to the rest of the UPDATE statement. The error thrown by the statement is that the <END-OF-STATEMENT> was reached unexpectedly. The SQL parser thinks that there should be more to the statement than it found. Here's a modified version of the same statement:
    UPDATE AMFLIBT.MOROUT SET OPSTC = 40
     WHERE condition-expression
    I replaced the subselect with condition-expression because that's what a WHERE clause needs, some kind of expression that indicates a condition that is either true or false. A condition generally looks like (A=B). One value is compared to another value to reach a 'true or false' condition. But the original statement simply had a SELECT statement that retrieved a few columns. There was no operation applied to those columns to set a condition. A condition might have been an EXISTS() that would result in 'true or false' depending on whether any rows were retrieved. Or a selected column might be tested for equality or inequality with some other value. What SQL expected was the rest of the condition. It was told what to SELECT but not what to do to test the selected values. There was no condition; there was just <END-OF-STATEMENT>. Tom
    125,585 pointsBadges:
    report
  • Dawn8160
    Tom and Sheldon Provides the correct answer... UPDATE AMFLIBT.MOROUT SET OPSTC = 40 WHERE (select AMFLIBT.MOROUT.ORDNO, AMFLIBT.MOROUT.OPSEQ, AMFLIBT.MOROUT.OPSTC, amflibt.morout.wkctr, amflibt.momast.ostat, AMFLIBT.MOMAST.FITEM FROM AMFLIBT.MOROUT INNER JOIN AMFLIBT.MOMAST ON AMFLIBT.MOROUT.ORDNO = AMFLIBT.MOMAST.ORDNO where fitem = ‘300.605.407.8′ and opseq < ‘0047′ and opstc = 10 and wkctr = ‘INFO’ and ostat <> 10 )
    0 pointsBadges:
    report
  • carlosdl
    Well, it would be hard to know whether an answer is correct without knowing what was the goal of the command. Tom has explained why the command was giving errors, but to be able to provide a correct command to replace it, we would need to know what was that command supposed to do. ------------- I have just read the question again, and it seems that Tom's answer was correct. The OP was asking where he/she was going wrong, and that's what Tom addressed.
    68,430 pointsBadges:
    report
  • TomLiotta
    My answer was purely for the sake of the 'Wiki' concept. This kind of discussion ought to be filled out so future searched might illustrate some concepts -- maybe. I can't quite tell if this site should be a daily "Q&A" service or if it should provide long-term reference and discussion. There seems to be a split personality that is never quite resolved in a satisfactory way. Perhaps the site really needs to consider splitting into two separate services that can reference each other. Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Maybe the ITKE blogs cover that long-term reference section to some degree. Unfortunately, there are many areas for which an ITKE blog doesn't exist.
    68,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following