Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    41

    Unanswered: db2 insert command not working

    Hello,

    I am wondering if someone can help

    I have had to create a view from a table called v1.
    I know need to create another table called t1 which will contain the values from v1 + a primary key.

    here is sample code from the v1

    Code:
    CREATE VIEW V1 AS
    SELECT
    LASTUPDATETIME as LASTUPDATETIME,
    TO_CHAR(LASTUPDATETIME,'YYYY')AS YEAR,
    TO_CHAR(LASTUPDATETIME,'YYYYQ') AS QUARTER_KEY,
    TO_CHAR(LASTUPDATETIME,'YYYYMM') AS MONTH_KEY,
    here is the sample code for the table

    Code:
    CREATE TABLE T1 (
    	DATETIMEID INT NOT NULL GENERATED ALWAYS AS IDENTITY (  
    		    START WITH +1  
    		    INCREMENT BY +1  
    		    NO CYCLE  
    		    NO CACHE  
    		    NO ORDER ) , 
    	YEAR VARCHAR(254),	
    	QUARTER_KEY VARCHAR(254),
    	MONTH_KEY VARCHAR(254));
    THESE COLUMNS NEED TO BE VARCHARS as a requirement from another app the fields will feed into.

    When I try and insert statement it wont work, no matter what I try..

    sample code
    Code:
    INSERT INTO T1(YEAR,QUARTER_KEY,MONTH_KEY)
    SELECT YEAR,QUARTER_KEY,MONTH_KEY FROM V1
    I get the error "The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007"

    If I put in the values manually it works OK.

    Here is sample screen shot of data from v1
    Attached Thumbnails Attached Thumbnails v1.png  

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    What is the data type of the column "LASTUPDATETIME"? could you show the ddl of the base table of view V1。

  3. #3
    Join Date
    Aug 2012
    Posts
    41
    Hi fengsun2

    Thanks for the response

    The LASTUPDATETIME is a vachar (32)
    sample value from the field 2013-08-11-20.27.54.726999
    I have no control over this table and it comes an external source. So I have to make do with what I am giving.

    I tried to create a new view and set LASTUPDATE TIME AS A TIMESTAMP, TRUNCATE IT TO MIN and then do the to_char () but this made no difference.

    What I really wanted to do was
    TRUNC LASTUPDATETIME to min
    select distinct on LASTUPDATETIME
    then do the to_char on the LASTUPDATETIME
    but I am having difficulity getting the code to work and support this.



    Code:
    SELECT
    trunc_timestamp(timestamp(LASTUPDATETIME),'MI')AS DATE_TIME
    FROM STAGING."SIGN";

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by alexandra123 View Post
    Hello,

    I am wondering if someone can help

    I have had to create a view from a table called v1.
    I know need to create another table called t1 which will contain the values from v1 + a primary key.

    here is sample code from the v1

    Code:
    CREATE VIEW V1 AS
    SELECT
    LASTUPDATETIME as LASTUPDATETIME,
    TO_CHAR(LASTUPDATETIME,'YYYY')AS YEAR,
    TO_CHAR(LASTUPDATETIME,'YYYYQ') AS QUARTER_KEY,
    TO_CHAR(LASTUPDATETIME,'YYYYMM') AS MONTH_KEY,
    Try casting the timestampstring into a date before applying to_char

    Code:
    with t(s) as (values cast('2013-08-11-20.27.54.726999' as varchar(32)) )
        , u(s,d) as ( select s, cast(substr(s,1,10) as date)  from t ) 
    select s, d, TO_CHAR(d,'YYYY') AS YEAR
                   , TO_CHAR(d,'YYYYQ') AS QUARTER_KEY
                   , TO_CHAR(d,'YYYYMM') AS MONTH_KEY 
    from u
    Last edited by lelle12; 08-27-13 at 02:24.
    --
    Lennart

  5. #5
    Join Date
    Aug 2012
    Posts
    41
    hi leenie12,

    I tried what you suggested using my own code but the sql insert statments still wont work

    Code:
    CREATE VIEW V1 AS
    select
    timestamp as lasupdatetime,
    TO_CHAR(timestamp,'YYYY')AS YEAR,
    TO_CHAR(timestamp,'YYYYQ') AS QUARTER_KEY,
    TO_CHAR(timestamp,'YYYYMM') AS MONTH_KEY,
    CONCAT(TO_CHAR(timestamp,'YYYYMM'),CONCAT('W', TO_CHAR(timestamp,'WW'))) AS WEEK_KEY,
    TO_CHAR(timestamp,'YYYYMMDD') AS DAY_KEY,
    TO_CHAR(timestamp,'YYYYMMDDHH24') AS HOUR_KEY,
    concat(to_char(timestamp,'YYYYMMDDHH24'),cast(floor(minute(timestamp)/15 ) * 15 AS char(2))) AS MIN_KEY,
    concat(to_char(timestamp,'YYYY-'),concat('Q',to_char(timestamp,'Q'))) AS QUARTER,
    to_char(timestamp,'YYYY-Mon') AS MONTH,
    concat(to_char(timestamp,'YYYY-Mon-'),concat('W',to_char(timestamp,'WW')))AS WEEK,
    to_char(timestamp,'YYYY-Mon-DD')AS DAY,
    concat(to_char(timestamp,'Mon-DD HH24:'),'00') AS HOUR,
    concat(to_char(timestamp,'Mon-DD HH24:'),cast(floor( minute(timestamp)/15 )*15 AS char(2))) AS MIN
     from (
    SELECT
    trunc_timestamp(timestamp(LASTUPDATETIME),'MI')AS timestamp
    FROM T1");

    Table where data will be inserted
    Code:
    CREATE TABLE T2 (
    	DATETIMEID INT NOT NULL GENERATED ALWAYS AS IDENTITY (  
    		    START WITH +1  
    		    INCREMENT BY +1  
    		    NO CYCLE  
    		    NO CACHE  
    		    NO ORDER ) , 
    	YEAR VARCHAR(254),	
    	QUARTER_KEY VARCHAR(254),
    	MONTH_KEY VARCHAR(254),
    	WEEK_KEY VARCHAR(509),
    	DAY_KEY VARCHAR(254),
    	HOUR_KEY VARCHAR(254),
    	MIN_KEY VARCHAR(256),
    	QUARTER VARCHAR(509),
    	MONTH VARCHAR(254),
    	WEEK VARCHAR(509),
    	DAY VARCHAR(254),
    	HOUR VARCHAR(256),
    	MIN VARCHAR(256));

    Insert statement
    Code:
    INSERT INTO T2(YEAR,QUARTER_KEY,MONTH_KEY,WEEK_KEY,DAY_KEY,HOUR_KEY,MIN_KEY,QUARTER,MONTH,WEEK,HOUR,DAY,MIN)
    SELECT YEAR,QUARTER_KEY,MONTH_KEY,WEEK_KEY,DAY_KEY,HOUR_KEY,MIN_KEY,QUARTER,MONTH,WEEK,HOUR,DAY,MIN FROM V1

    ERROR
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

    SQL0180N The syntax of the string representation of a datetime value is incorrect.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It is possible that some values in the column TIMESTAMP do not conform to the proper format, which would cause the error you're seeing.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Before trying "INSERT INTO T2 ... SELECT ... FROM V1",
    please try
    "SELECT * FROM V1" (If the resutls were too many, you might want to try "SELECT * FROM V1 FETCH FIRST nnn ROWS ONLY"),
    "DESCRIBE SELECT * FROM V1",
    "DESCRIBE TABLE T2" and
    "DESCRIBE TABLE T1".

    What results did you go?
    Last edited by tonkuma; 08-26-13 at 23:14. Reason: Add "DESCRIBE TABLE T1"

  8. #8
    Join Date
    Aug 2012
    Posts
    41
    I hope this helps
    Attached Thumbnails Attached Thumbnails describe.png  

  9. #9
    Join Date
    Aug 2012
    Posts
    41
    trying to insert the image again
    Attached Thumbnails Attached Thumbnails describe.jpg  

  10. #10
    Join Date
    Aug 2012
    Posts
    41
    I ended up getting this to work, thanks to the help of a colleague and all involved in the post.

    The problem was that you cannot do an insert into a table from a view when you are excluding columns from the view.

    If i do the
    Code:
    insert into select * from
    it works fine.

    This thread can now be closed

Posting Permissions

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