[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

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.

thank you feldman

But you can create either a materialised query table or a view with "CREATE ... AS SELECT ..."

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

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

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).

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).