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

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
    282 Posts

    Re: CAST

    ‏2013-05-15T07:34:44Z  

    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
    450 Posts

    Re: CAST

    ‏2013-05-15T18:15:21Z  
    • B.Hauser
    • ‏2013-05-15T07:34:44Z

    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

     

    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

    Re: CAST

    ‏2013-05-17T03:18:29Z  

    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

     

     

    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
    450 Posts

    Re: CAST

    ‏2013-05-17T14:51:13Z  
    • DougTX
    • ‏2013-05-17T03:18:29Z

    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

    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

    Re: CAST

    ‏2013-05-17T16:06:10Z  

    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.

    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
    450 Posts

    Re: CAST

    ‏2013-05-17T20:14:20Z  
    • DougTX
    • ‏2013-05-17T16:06:10Z

    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.

     

     

    What is type and length of RCSEQ5?

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