SQL insert error

Tags:
AS/400
SQL
Hi, I am setting a process of creating a table and inserting data into it from another table. I created a table "example". When I run the SQL command insert into mylib/example select (some specific columns) from example2 where User = 'sqakxb'. It is throwing exception error. When I run the command insert into mylib/example select (some specified columns) where User = 'sqakxb'. It is throwing conversion error *N. Is there some thing wrong with my statement?
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 6  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.
  • TheRealRaven
    It's hard to tell if you posted the exact statements or if you simply typed examples of statements. If you didn't copy/paste the exact statements, we can't be sure.

    The second one is definitely wrong since the SELECT has no FROM clause. The first one might be incorrect if the SELECT statement really is not enclosed in parentheses.

    You should also post the SQL message IDs (or SQLSTATEs/SQLCODEs) for all errors.
    34,940 pointsBadges:
    report
  • Kaustav00001
    I have 2 QMQRYs .The first one is CREATE which creates a table "create" with certain number of columns. The second is INSERT. The INSERT QMQRY contains . " insert into create (select (some columns from order where OHCRDT=&curdate and OHCRUS=&USER). OHCRUS is a character field OHCRDT is a numeric field. Curdate is variable which is calculated and passed from within a CL whereas USER is a parameter passed to the same CL.

      Now the CL "ORDERREP" contains two STRQMQRY statement for CREATE and INSERT. When I am calling the CL like "CALL ORDERREP PARM('SQAKXB') . It is throwing error QWM2701. What wrong am I doing?
    65 pointsBadges:
    report
  • Kaustav00001
    Typing mistake.

    The INSERT QMQRY contains - " insert into create (select (some columns) from order where OHCRDT=&CURDATE and OHCRUS=&USER)
    65 pointsBadges:
    report
  • TheRealRaven
    Now it's hard to tell without seeing the CL. Most likely, you haven't supplied single-quote marks around the &USER CL variable that you pass into the QM query.

    A QM query contains a literal string for a SQL statement. When a character column such as OHCRUS is compared against a value, the value must be enclosed in single quote marks. You can't put the quote marks inside the QM query like this:
    and OHCRUS='&USER')
    ...because it then wouldn't be able to determine if &USER was a variable name or if you actually wanted to compare against the literal value '&USER'. So you need to add the quotes in CL before passing the value into the query.

    There are some simple methods of doing that, but it would help if we could see the related CL statements.
    34,940 pointsBadges:
    report
  • Kaustav00001
    The line is 
     PGM        PARM(&USERID)                    
     DCL        VAR(&USERID) TYPE(*CHAR) LEN(6)  
    STRQMQRY   QMQRY(create) 
     STRQMQRY   QMQRY(insert) SETVAR((CURDATE  &CURRDATE) (USER &USERID)). Is this wrong?      


    Then CALL ORDERREP PARM('SQAKXB')     
    65 pointsBadges:
    report
  • TheRealRaven
    Can't be certain because we can't see how &USERID values are set. But it seems likely that it won't work be cause there is nothing that code that puts quotes around the &USERID value.

    If your user ID values are really only CHAR(6) long, then you'll need to create a new variable that is CHAR(8) long. You'll take the &USERID value, concatenate apostrophes to it as the first and last characters, and then pass that new variable into the STRQMQRY command.

    Since it's a CHAR column in your table, the value must be quoted when you insert into your query statement string. That's just how SQL statements work. QM queries don't use parameter markers; they're fully dynamic statement strings that you create. So you must do the quoting.

    There are techniques that can simplify the coding, but you should learn the details first. Then it'll make more sense when you see a simplification.
    34,940 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.

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

Following

Share this item with your network: