Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: How to use Conversion and cast functions while creating table in DB2 9.7

    Hi,
    Now I am using DB2 9.7 (windows)
    I need to create the table using of Conversion and cast functions but it throws an error ,can any one help me to create the table

    eg :

    CREATE TABLE IBM_TABLE (
    BATCH varchar(20) ,
    EXPIRY timestamp ,
    TIMESTAMP varchar(30) ,
    PI_AGAINST_ALLITEMS integer ,
    V_FLAG integer default 0 ,
    TSID integer default to_number(to_char(sysdate,'yyyymmddhh24miss')) )

    Error :

    com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,
    SQLERRMC=to_number;SID integer default;CHECK, DRIVER=3.57.82

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TSID integer default to_number(to_char(sysdate,'yyyymmddhh24miss'))
    How about changing integer to bigint?

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    I tried already but
    No reaction ,same error

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DEFAULT doesn't allow functions.
    GENERATED [ALWAYS] AS ( generation-expression ) may be an alternative.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or
    DEFAULT CAST(sysdate AS BIGINT)

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    Ya i tried the same

    1)
    alter table items ADD COLUMN TSID BIGINT GENERATED ALWAYS AS to_number(to_char(sysdate,'yyyymmddhh24miss'))

    2)

    TSID bigint default CAST(sysdate AS BIGINT) in create table

    but it shows same error msg

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I found following descriptions on "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 2".
    default-clause
    Specifies a default value for the column.

    .....
    .....

    datetime-special-register
    Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP)
    at the time of INSERT, UPDATE, or LOAD as the default for the column.
    The data type of the column must be the data type that corresponds to the special register specified
    (for example, data type must be DATE when CURRENT DATE is specified).

    .....
    and
    The generation-expression cannot contain any of the following (SQLSTATE 42621):
    v Subqueries
    v XMLQUERY or XMLEXISTS expressions
    v Column functions
    v Dereference operations or DEREF functions
    v User-defined or built-in functions that are non-deterministic
    v User-defined functions using the EXTERNAL ACTION option
    v User-defined functions that are not defined with NO SQL
    v Host variables or parameter markers
    v Special registers and built-in functions that depend on the value of a special register
    v Global variables
    v References to columns defined later in the column list
    v References to other generated columns
    v References to columns of type XMLThe
    So, the following examples are valid:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE IBM_TABLE (
    BATCH varchar(20) ,
    EXPIRY timestamp ,
    TIMESTAMP varchar(30) ,
    PI_AGAINST_ALLITEMS integer ,
    V_FLAG integer default 0 
    ,TSID timestamp DEFAULT sysdate
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    or
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE IBM_TABLE (
    BATCH varchar(20) ,
    EXPIRY timestamp ,
    TIMESTAMP varchar(30) ,
    PI_AGAINST_ALLITEMS integer ,
    V_FLAG integer default 0 
    ,gen_current_ts timestamp DEFAULT sysdate
    ,TSID bigint GENERATED ALWAYS AS ( BIGINT(gen_current_ts) )
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    But, the following examples are not valid:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE IBM_TABLE2 (
    BATCH varchar(20) ,
    EXPIRY timestamp ,
    TIMESTAMP varchar(30) ,
    PI_AGAINST_ALLITEMS integer ,
    V_FLAG integer default 0 
    ,TSID bigint DEFAULT BIGINT(sysdate)
    );
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0574N  DEFAULT value or IDENTITY attribute value is not valid for column 
    "TSID" in table "DB2ADMIN.IBM_TABLE2".  Reason code: "7".  SQLSTATE=42894
    or
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE IBM_TABLE (
    BATCH varchar(20) ,
    EXPIRY timestamp ,
    TIMESTAMP varchar(30) ,
    PI_AGAINST_ALLITEMS integer ,
    V_FLAG integer default 0 
    ,TSID bigint GENERATED ALWAYS AS ( BIGINT(sysdate) )
    );
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0548N  A check constraint or generated column that is defined with 
    "sysdate" is invalid.  SQLSTATE=42621
    I couldn't find any rational reason.

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    Hi ,

    Thank u for Giving me the solution,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •