ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

INSERT Error with WHERE

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

  • INSERT Error with WHERE

    Is WHERE not allowed with INSERT unless there's SELECT FROM ?

    SEU Shows this error "Keyword WHERE not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT..."
    I can Check the value in C-SPEC IF-END code, but that seems more clunky

    Code:
     C/EXEC SQL                                                    
     C+ INSERT INTO MyTable VALUES(:BPLYEAR, :BPLPERD,            
     C+ :BPLPERN, :ATREG, :ACMSPAR, :AXSPITM, :BOFTOTA, :BOFACCA,  
     C+ :HOFORDR, :AHDTYP, :AHTOWH, ' ', ' ', :AIWGHT)            
     C+   [COLOR=#FF0000]WHERE :BPLYEAR >= '2020'  [/COLOR]
    Last edited by MFisher; July 10, 2020, 10:47 AM.

  • #2
    No WHERE is not part of the basic INSERT statement, you will need to wrap the statement with an RPG if statement.

    Comment


    • #3
      I have never done an insert without naming the columns. I think you need to state them as seen below.

      exec sql
      insert into CHGPMT1
      (fnbk,
      fnrun,
      fnseq,
      fnapp,
      fncri,
      fnamt,
      fnpcc,
      fnacct,
      fnrtr,
      fndes1,
      fncost)
      values
      (2,
      888,
      :ftp003_fnseq,
      024,
      :ftp003_fncri,
      :ftp003_amount,
      :ftp003_fnpcc,
      :f1_accountnum,
      :ftp003_fnrtr,
      :ftp003_fndes1,
      :ftp003_fncost);

      Comment


      • Scott M
        Scott M commented
        Editing a comment
        If you all inserting into all columns in the table it is not necessary to list the column names. Although by listing them it is easier to make sure you have all the 'values' in the proper sequence.

    • #4

      But, I don't know what does it mean a WHERE without a SELECT...

      It has no sense.


      Originally posted by MFisher View Post
      Is WHERE not allowed with INSERT unless there's SELECT FROM ?

      SEU Shows this error "Keyword WHERE not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT..."
      I can Check the value in C-SPEC IF-END code, but that seems more clunky

      Code:
       C/EXEC SQL
      C+ INSERT INTO MyTable VALUES(:BPLYEAR, :BPLPERD,
      C+ :BPLPERN, :ATREG, :ACMSPAR, :AXSPITM, :BOFTOTA, :BOFACCA,
      C+ :HOFORDR, :AHDTYP, :AHTOWH, ' ', ' ', :AIWGHT)
      C+ [COLOR=#FF0000]WHERE :BPLYEAR >= '2020' [/COLOR]

      Comment


      • Vectorspace
        Vectorspace commented
        Editing a comment
        I think MFisher wanted to only perform the INSERT if some condition was true. But to make the code simpler, he wanted to do it within the SQL statement itself. So he hoped he could use a WHERE to condition the INSERT: "Perform this insert only if this condition is true). But you can't do that.
    Working...
    X