Topic
6 replies Latest Post - ‏2013-05-17T20:14:20Z by krmilligan
DougTX
DougTX
4 Posts
ACCEPTED ANSWER

Pinned topic CAST

‏2013-05-14T16:27:38Z |

 

I am writing an SQL INSERT using a subselect. The subselect is only populating specific columns in the table I am inserting into. The other columns in the table that I am not populating will either be blanks for character fields, or zero for numeric.
 
Some of the numeric fields where I am initializing to zero have decimal places, while others do not.
 
In order to get the INSERT statement to return SQLSTATE of zeros, I needed to add the CAST command to populate the blanks or zeros in the fields I am not pulling in. However, when I execute the prepared statement, I get a SQLSTATE value of 22018. I have done everything I can think of for the CAST. The only thing I have not tried is to include the decimal point for the numeric fields. Is CAST that particular? I would think that if I used:
 
cast(0 as dec(15,2)),
 
it would be fine. Do I need to replace that with something like:
 
cast(0.00 as dec(15,2)), or
cast(0000000000000.00 as dec(15,2))?
 
We are on IBMi V7R1.

  • B.Hauser
    B.Hauser
    218 Posts
    ACCEPTED ANSWER

    Re: CAST

    ‏2013-05-15T07:34:44Z  in response to DougTX

    I'm not clear why you need a cast at all. When instering a *ZERO into any numeric field it is automatically converted to the expected format.

    Insert into MyTable Values(0,  'ABC');

    Birgitta

     

    • krmilligan
      krmilligan
      335 Posts
      ACCEPTED ANSWER

      Re: CAST

      ‏2013-05-15T18:15:21Z  in response to B.Hauser

      What SQLSTATE value were you getting on that INSERT that caused you to start using the CAST function?

      Could you eliminate the specification of blanks and zeroes by just omitting the columns and letting DB2 assign the default values with a statement like the following?  

         INSERT into target_table (colA, colC, colE) SELECT col1, col3, col5 FROM source_table

       

       

      • DougTX
        DougTX
        4 Posts
        ACCEPTED ANSWER

        Re: CAST

        ‏2013-05-17T03:18:29Z  in response to krmilligan

        What I am trying to do is to insert records into a table from another table. The original cast I did where I was casting a zero to ALL unused fields, whether alpha or numeric worked fine. However, when I looked at the joblog, there was a message that said that led me to believe I'd get better performance if I used CAST. So then I changed the zeros to CAST as necessary, but could never get a clean execution of the SQL. There was always the SQLSTATE I listed above that came back from SQL. Finally, I gave up [I was under time constraints], and created the workfile to have only the fields I needed in it, and everything worked out. I wanted to use the CREATE TABLE function here, where I can create one table like another, which worked. But since this INSERT is being made from a HUGE table, which already takes awhile to process, I thought using the cast, like the message said, might save some time. Let me know if you need me to research what the JOBLOG message was, as I may still have that joblog on the spool.

        Thank you,

        Doug

        • krmilligan
          krmilligan
          335 Posts
          ACCEPTED ANSWER

          Re: CAST

          ‏2013-05-17T14:51:13Z  in response to DougTX

          I'm not sure what message you're referring to, so it would be helpful to share the Message ID. 

          I'm guessing it was supplying a value of 0 to the character fields causing the message.  DB2 can convert 0 to a character string, but it would be faster to use a value of '0' instead.

          • DougTX
            DougTX
            4 Posts
            ACCEPTED ANSWER

            Re: CAST

            ‏2013-05-17T16:06:10Z  in response to krmilligan

            Here is the message I received:

            SQL7939    Information        00   05/13/13  14:54:09.584440  QSQRUN3      QSYS        *STMT    QSQROUTE  
              From module . . . . . . . . :   QSQINS                                    
              From procedure  . . . . . . :   CLEANUP                                   
              Statement . . . . . . . . . :   29834                                     
              To module . . . . . . . . . :   QSQROUTE                                  
              To procedure  . . . . . . . :   QSQROUTE                                  
              Statement . . . . . . . . . :   12900                                     
              Message . . . . :   Data conversion required on INSERT or UPDATE.         
              Cause . . . . . :   The INSERT or UPDATE values can not be directly moved to
                the columns because the data type or length of a value is different than one
                of the columns. The INSERT or UPDATE statement ran correctly.  Performance,
                however, would be improved if no data conversion was required. The reason
                data conversion is required is 2. -- Reason 1 is that the INSERT or UPDATE
                value is a character or graphic string of a different length than column
                RCSEQ5. -- Reason 2 is that the INSERT or UPDATE value is a numeric type
                that is different than the type of column RCSEQ5. -- Reason 3 is that the
                INSERT or UPDATE value is a variable length string and column RCSEQ5 is not.
                -- Reason 4 is that the INSERT or UPDATE value is not a variable length 
                string and column RCSEQ5 is. -- Reason 5 is that the INSERT or UPDATE value
                is a variable length string whose maximum length is different that the  
                maximum length of column RCSEQ5. -- Reason 6 is that a data conversion was
                required on the mapping of the INSERT or UPDATE value to column RCSEQ5, such
                as a CCSID conversion. -- Reason 7 is that the INSERT or UPDATE value is a
                character string and column RCSEQ5 is of type DATE, TIME, or TIMESTAMP. --
                Reason 8 is that the target table of the INSERT is not a SQL table. Recovery
                 . . . :   To get better performance, try to use values of the same type and
                length as their corresponding columns.

             

             

            • krmilligan
              krmilligan
              335 Posts
              ACCEPTED ANSWER

              Re: CAST

              ‏2013-05-17T20:14:20Z  in response to DougTX

              What is type and length of RCSEQ5?

              I am not understanding why you wouldn't just subset the columns specified on the Insert?

Feed for this topic