what is wrong with this sql statement? help!

[zicq]

I am new to DB2, used MS Sql before. Can someone help me understand the difference
of the below 2 sql statements? The one in multiple lines won't work, but
the one in one line works! Does DB2 SQL has to be written in one line only?
What if I have a lot of fields in this table? Thank you for your help.

CREATE TABLE TEST1(ROOT VARCHAR(10), LOCATION VARCHAR(4))
vs.
CREATE TABLE TEST1
(ROOT VARCHAR(10),
LOCATION VARCHAR(4))
----------------------------------------------------------
The error message for the multi-line statement is below:
---------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not
a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "TABLE" was found following "CREATE ". Expected
tokens may include: "TYPE". SQLSTATE=42601

DB21034E The command was processed as an SQL statement because it was not
a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "ROOT VARCHAR" was found following "(".
Expected tokens may include: "<values>". SQLSTATE=42601

DB21034E The command was processed as an SQL statement because it was not
a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "LOCATION VARCHAR" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

[MarkN]

How are you running the statement? (what tool). You need a statement termination
character, which I think the default is ';'. You also need to inform the
system you are doing so, depending on the tool.

"zicq" <zicq17635@icqmail.com> wrote:
>
>I am new to DB2, used MS Sql before. Can someone help me understand the
difference
>of the below 2 sql statements? The one in multiple lines won't work, but
>the one in one line works! Does DB2 SQL has to be written in one line only?
>What if I have a lot of fields in this table? Thank you for your help.
>
>CREATE TABLE TEST1(ROOT VARCHAR(10), LOCATION VARCHAR(4))
>vs.
>CREATE TABLE TEST1
>(ROOT VARCHAR(10),
>LOCATION VARCHAR(4))
>----------------------------------------------------------
>The error message for the multi-line statement is below:
>---------------------------------------------------------
>DB21034E The command was processed as an SQL statement because it was not
>a valid Command Line Processor command. During SQL processing it returned:
>SQL0104N An unexpected token "TABLE" was found following "CREATE ". Expected
>tokens may include: "TYPE". SQLSTATE=42601
>
>DB21034E The command was processed as an SQL statement because it was not
>a valid Command Line Processor command. During SQL processing it returned:
>SQL0104N An unexpected token "ROOT VARCHAR" was found following "(".
>Expected tokens may include: "<values>". SQLSTATE=42601
>
>DB21034E The command was processed as an SQL statement because it was not
>a valid Command Line Processor command. During SQL processing it returned:
>SQL0104N An unexpected token "LOCATION VARCHAR" was found following
>"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
>SQLSTATE=42601
>
>

[Greg Nash]

I assume you're using the db2cmd prompt. Make sure you open it with a
specified termination character (option -td). E.g.

db2 -td;

> connect to mydatabase ;

> CREATE TABLE TEST1
> (ROOT VARCHAR(10),
> LOCATION VARCHAR(4)) ;

You then need to put a ; at the end of each command.

You can use other characters, e.g.
db2 -td@

> connect to mydatabase @
etc.

--greg



zicq wrote:
> I am new to DB2, used MS Sql before. Can someone help me understand the difference
> of the below 2 sql statements? The one in multiple lines won't work, but
> the one in one line works! Does DB2 SQL has to be written in one line only?
> What if I have a lot of fields in this table? Thank you for your help.
>
> CREATE TABLE TEST1(ROOT VARCHAR(10), LOCATION VARCHAR(4))
> vs.
> CREATE TABLE TEST1
> (ROOT VARCHAR(10),
> LOCATION VARCHAR(4))
> ----------------------------------------------------------
> The error message for the multi-line statement is below:
> ---------------------------------------------------------
> DB21034E The command was processed as an SQL statement because it was not
> a valid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "TABLE" was found following "CREATE ". Expected
> tokens may include: "TYPE". SQLSTATE=42601
>
> DB21034E The command was processed as an SQL statement because it was not
> a valid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "ROOT VARCHAR" was found following "(".
> Expected tokens may include: "<values>". SQLSTATE=42601
>
> DB21034E The command was processed as an SQL statement because it was not
> a valid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "LOCATION VARCHAR" was found following
> "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
> SQLSTATE=42601
>
>