Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    12

    Lightbulb Unanswered: IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SELECT" was found

    [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SELECT" was found following "test AS". Expected tokens may include: "(". SQLSTATE=42601



    im getting the above error when i tried to execute the query below

    CREATE TABLE test
    AS
    SELECT
    'Zigma' Company,
    'ANY' IP,
    'ANY' Hostname,
    CAST(TIMESTAMP(SUBSTR(CHAR('1/22/2007'),6,4) || '-' || SUBSTR(CHAR('1/22/2007'),3,2) || '-' || SUBSTR(CHAR('1/22/2007'),1,1) || ' 00:00:00') AS DATE) Expire,
    '10.1' Version,
    'y' IsLicensed,
    CURRENT TIMESTAMP LastUpdated
    FROM SYSIBM.SYSDUMMY1
    FETCH FIRST 1 ROWS ONLY
    Last edited by rajesh438; 09-27-06 at 14:14.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Unless this is a new feature in V9, you cannot create a table with a select statement. You can use CREATE TABLE LIKE...

    Please refer to the SQL Reference manual of the appropriate DB2 version you are using.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2006
    Posts
    12
    thank you feldman

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    But you can create either a materialised query table or a view with "CREATE ... AS SELECT ..."
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    hi Rajesh
    just got curious why u are using

    "CAST(TIMESTAMP(SUBSTR(CHAR('1/22/2007'),6,4) || '-' || SUBSTR(CHAR('1/22/2007'),3,2) || '-' || SUBSTR(CHAR('1/22/2007'),1,1) || ' 00:00:00') AS DATE) Expire "

    u are converting it from date to timestamp, then again to date??

    following options may be much more handy

    values timestamp_iso(date('1/22/2007'))

    1
    --------------------------
    2007-01-22-00.00.00.000000

    or
    values date('1/22/2007')

    1
    ----------
    2007-01-22

    Regards
    Rahul Singh

  6. #6
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    one more thing
    "FROM SYSIBM.SYSDUMMY1
    FETCH FIRST 1 ROWS ONLY"

    sysdummy1 contains only one row . so no need to add fetch....

    regards,
    Rahul Singh

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rahul_s80
    values date('1/22/2007')
    There is no guarantee that the output will look like "2007-01-22"; this is an installation-related global DB2 setting.
    Use "CHAR('1/22/2007', ISO)" to force an ISO date format (or indeed the timestamp_iso function).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When inserting or updating data into a date column, the ISO format 'YYYY-MM-DD' will also be recognized and stored correctly in DB2.

    As mentioned by others, the output format depends on the installation location settings of the client (either the client which is on the server, or a remote client).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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