ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Error. Token Not Valid

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Error. Token Not Valid

    I am trying to write an UPDATE statement for Table 1, based on a SUM of a column in Table 2. I have it working in MS SQL Server, against SQL tables that are copies of iSeries files, but when I copy-paste to Navigator and run against iSeries files, I get an error.

    This is my iSeries/Navigator code. Did I make a silly typo ?

    Code:
    UPDATE MYLIB.INVCPX00
    SET  WHOCPL = (select SUM(Z.WHOCPL)          
            FROM  MYLIB.INVCPZ00 Z
           WHERE Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME and  Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR and
            Z.WHFLPD = X.WHFLPD and  Z.WHFPDY = X.WHFPDY and  Z.WHWHSE = X.WHWHSE and  Z.WHSEQ = X.WHSEQ and  Z.WHLTY = X.WHLTY
            AND    Z.WHSEQ IN ('1', '2')
           AND Z.WHZONE IN ('BLD',  'FGW', 'RMW', 'FGA', 'RMA' )
            GROUP BY Z.WHDATE, Z.WHTIME, Z.WHFLYR, Z.WHFLQR,  Z.WHFLPD, Z.WHFPDY, Z.WHWHSE, Z.WHSEQ, Z.WHLTY )
    FROM MYLIB.INVCPX00  X
    JOIN  MYLIB.INVCPZ00 Z on Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME and  Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR and
          Z.WHFLPD = X.WHFLPD and  Z.WHFPDY = X.WHFPDY and  Z.WHWHSE = X.WHWHSE and  Z.WHSEQ = X.WHSEQ and  Z.WHLTY = X.WHLTY
    WHERE    X.WHSEQ IN ('1', '2')
    ;

    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.
    Last edited by MFisher; December 31, 2019, 08:49 AM.

  • #2
    Part 2. I need to run this in an RPG program. Are there syntax changes I need to make for the RPG version ?

    Comment


    • #3
      Hi Fisher,

      I didn0t know MS SQL Server, but probably you can resolve it with a MERGE statement, you have to try it.. something like this:

      Code:
       MERGE INTO MYLIB.INVCPX00 x USING (Select Z.WHDATE, Z.WHTIME, Z.WHFLYR, Z.WHFLQR,  Z.WHFLPD, Z.WHFPDY, Z.WHWHSE, Z.WHSEQ, Z.WHLTY, SUM(Z.WHOCPL) as whocplsum                  FROM  MYLIB.INVCPZ00 Z        WHERE   Z.WHSEQ IN ('1', '2') AND Z.WHZONE IN ('BLD',  'FGW', 'RMW', 'FGA', 'RMA' )         GROUP BY Z.WHDATE, Z.WHTIME, Z.WHFLYR, Z.WHFLQR,  Z.WHFLPD, Z.WHFPDY, Z.WHWHSE, Z.WHSEQ, Z.WHLTY ) as z  
       on Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME and Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR and Z.WHFLPD = X.WHFLPD            and Z.WHFPDY = X.WHFPDY and Z.WHWHSE = X.WHWHSE and Z.WHSEQ = X.WHSEQ and Z.WHLTY = X.WHLTY  when matched then update set X.whocpl = z.whocplsum;
      I can't try it, but probably this is the correct way.

      In rpgle you have only to put it after an exec sql.

      Bye

      Comment


      • #4
        only for better read:

        Code:
        Merge Into Mylib.Invcpx00 X Using (
            Select Z.Whdate, Z.Whtime, Z.Whflyr, Z.Whflqr, Z.Whflpd, Z.Whfpdy, Z.Whwhse, Z.Whseq, Z.Whlty, Sum(Z.Whocpl) As Whocplsum
              From Mylib.Invcpz00 Z
              Where Z.Whseq In ('1', '2')
                    And Z.Whzone In ('BLD', 'FGW', 'RMW', 'FGA', 'RMA')
              Group By Z.Whdate, Z.Whtime, Z.Whflyr, Z.Whflqr, Z.Whflpd, Z.Whfpdy, Z.Whwhse, Z.Whseq, Z.Whlty
          ) As Z On Z.Whdate = X.Whdate
          And Z.Whtime = X.Whtime
          And Z.Whflyr = X.Whflyr
          And Z.Whflqr = X.Whflqr
          And Z.Whflpd = X.Whflpd
          And Z.Whfpdy = X.Whfpdy
          And Z.Whwhse = X.Whwhse
          And Z.Whseq = X.Whseq
          And Z.Whlty = X.Whlty
          When Matched Then Update Set X.Whocpl = Z.Whocplsum;

        Bye

        Comment


        • #5
          I had something similar and I believe it is a difference between SQL Server and DB2i. Here is an example that I have in a procedure that works for me. Changed the 'From' part of update to the Where Exists.

          Code:
          UPDATE MRLDTALIB . RCCARWYB X
          
          SET X . CWXFLD4 =
              ( SELECT
                  CASE WHEN T . TRXFLD1 = 'S' THEN 'Y' ELSE '' END AS NEWSUPPLEMENTAL
              FROM MRLDTALIB . RCCARWYB WB
                  LEFT JOIN MRLDTALIB . RCCARTRN T ON WB . CWEQPINT = T . TREQPINT AND WB . CWEQPNBR = T . TREQPNBR AND WB . CWWBIDTS = T . TRWBIDTS AND WB . CWWYBVER = T . TRWYBVER
              WHERE 1 = 1
                  AND X . CWEQPINT = WB . CWEQPINT
                  AND X . CWEQPNBR = WB . CWEQPNBR
                  AND X . CWWBIDTS = WB . CWWBIDTS
                  AND X . CWWYBVER = WB . CWWYBVER
              )
          WHERE EXISTS (
              SELECT 1
          
              FROM MRLDTALIB . RCCARWYB WB
              WHERE 1 = 1
                  AND WB . CWRCDSTS NOT IN ( 'D' , 'F' )
                  AND WB . CWGRPCDE NOT IN ( 'OL' , '' , 'ERR' , 'LS' , 'RS' )
                  AND X . CWEQPINT = WB . CWEQPINT
                  AND X . CWEQPNBR = WB . CWEQPNBR
                  AND X . CWWBIDTS = WB . CWWBIDTS
                  AND X . CWWYBVER = WB . CWWYBVER
              ) ;

          Comment


          • #6
            iACS's Run SQL Scripts can be set to use either SQL naming conventions or System (IBM i) naming conventions. The principle difference between the two in this context is that Schema and Table are separated with a " . " for SQL, and a " / " for System. Since the error says that a " . " is incorrect, this would be my first guess.

            Try specifying MYLIB/INVCPX00 instead of MYLIB.INVCPX00

            Though you say iSeries/Navigator, which might mean you are referring to the old IBM System i Access for Windows software package, as opposed to its modern replacement IBM iAccess Client Solutions (I am less familaiar with the old one)

            It also looks like you are trying to update through a join:
            Code:
            UPDATE MYLIB/INVCPX00
            SET  WHOCPL = (...)
            FROM MYLIB/INVCPX00  X
            JOIN  MYLIB/INVCPZ00 Z on Z.WHDATE = X.WHDATE and ...
            WHERE    X.WHSEQ IN ('1', '2')
            I assume you want to update those records in INVCPX00 where there is a match in INVCPZ00?
            db2 does not support updates though a join. You have to either use the merge statement (see https://www.ibm.com/support/knowledg...rbafymerge.htm), or use an exists clause in the where statement like this:
            Code:
            UPDATE MYLIB/INVCPX00 X
            SET  WHOCPL = (...)
            WHERE    X.WHSEQ IN ('1', '2')
            AND EXISTS (SELECT 1 FROM MYLIB/INVCPZ00 Z on Z.WHDATE = X.WHDATE and ...)

            Comment


            • #7
              @System versus SQL Naming convention (FYI)
              When working with System Naming Convention Schema and Object can be either separated by a slash (/) OR a period (.) - Since Release 7.1 TR 5!
              The main difference between System and SQL Naming conventions is in the unqualified specification. With System Naming Convention the Library List is searched, with SQL Naming Conventions the library list is ignore and tables/views/files are searched within a single schema (Current/Default Schema). SQL Routines (Functions, Stored Procedures) are searched within the SQL Path. Within the SQL Path mutliple schemas can be specified and special value *LIBL is allowed.

              Also as others already pointed out: A direct join within the Update Statement is NOT SQL Standard and is NOT supported on Db2 for i.
              The correct syntax is with 2 sub-selects (one after SET and the other with the EXISTS predicate within the WHERE Conditions.

              Birgitta

              Comment


              • #8
                Thanks to everyone for the help.

                Comment

                Working...
                X