CLP giving weird error code for simple SQL

#1

I'm using the CLP on a Windows XP machine, DB2 ESE 9.5. I connect to
the SAMPLE database and issue the following command :

db2 select * from proj where deptno <> 'E21'

and the response I get is :

Quote:

was unexpected at this time.

If I use the command editor application (GUI thingie) and enter the
same command (minus the "db2") it all works fine.

It seems that the CLP dislikes the <> operator for some reason.

Can anyone elucidate me on this?

Mairhtin

#2

On May 20, 6:03*pm, Morch <mairh... (AT) gmail (DOT) com> wrote:

Quote:

I'm using the CLP on a Windows XP machine, DB2 ESE 9.5. *I connect to
the SAMPLE database and issue the following command :

db2 select * from proj where deptno <> 'E21'

and the response I get is :

was unexpected at this time.

If I use the command editor application (GUI thingie) and enter the
same command (minus the "db2") it all works fine.

It seems that the CLP dislikes the <> operator for some reason.

Can anyone elucidate me on this?

Mairhtin

In CLP you should 'double quote' your SQL-statements to prevent wrong
interpretations like this by the OS.
Change your statement like this:
db2 "select * from proj where deptno <> E'21'"

HTH

--
Jeroen

#3

On May 20, 6:14*pm, The Boss <nlt... (AT) baasbovenbaas (DOT) demon.nl> wrote:

Quote:

On May 20, 6:03*pm, Morch <mairh... (AT) gmail (DOT) com> wrote:

I'm using the CLP on a Windows XP machine, DB2 ESE 9.5. *I connect to
the SAMPLE database and issue the following command :

db2 select * from proj where deptno <> 'E21'

and the response I get is :

was unexpected at this time.

If I use the command editor application (GUI thingie) and enter the
same command (minus the "db2") it all works fine.

It seems that the CLP dislikes the <> operator for some reason.

Can anyone elucidate me on this?

Mairhtin

In CLP you should 'double quote' your SQL-statements to prevent wrong
interpretations like this by the OS.
Change your statement like this:
db2 "select * from proj where deptno <> E'21'"

HTH

Sorry for the typo, E'21' should be 'E21' of course.

--
Jeroen

#4

Thanks for that. I tried single quotes around the command, like I
would do in a Unix Shell prompt, and when it failed, I raised my fists
to the heavens and cursed. I didn't try double quotes. Stupid
Windoze.

M

On May 20, 11:17*am, The Boss <nlt... (AT) baasbovenbaas (DOT) demon.nl> wrote:

Quote:

On May 20, 6:14*pm, The Boss <nlt... (AT) baasbovenbaas (DOT) demon.nl> wrote:

On May 20, 6:03*pm, Morch <mairh... (AT) gmail (DOT) com> wrote:

I'm using the CLP on a Windows XP machine, DB2 ESE 9.5. *I connect to
the SAMPLE database and issue the following command :

db2 select * from proj where deptno <> 'E21'

and the response I get is :

was unexpected at this time.

If I use the command editor application (GUI thingie) and enter the
same command (minus the "db2") it all works fine.

It seems that the CLP dislikes the <> operator for some reason.

Can anyone elucidate me on this?

Mairhtin

In CLP you should 'double quote' your SQL-statements to prevent wrong
interpretations like this by the OS.
Change your statement like this:
db2 "select * from proj where deptno <> E'21'"

HTH

Sorry for the typo, E'21' should be 'E21' of course.

--
Jeroen

#5

You have to use double quotes in a UNIX shell as well.

On 20.05.10 14:17 , Morch wrote:

Quote:

Thanks for that. I tried single quotes around the command, like I
would do in a Unix Shell prompt, and when it failed, I raised my fists
to the heavens and cursed. I didn't try double quotes. Stupid
Windoze.

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

#6

On 2010-05-21 02:07, Helmut Tessarek wrote:

Quote:

You have to use double quotes in a UNIX shell as well.

My first reaction as well, but it actually worked with quotes:

[lelle@...]$ db2 'select count(*) from price'

1
-----------
11765

1 record(s) selected.

Quote:

On 20.05.10 14:17 , Morch wrote:
[...]
Stupid Windoze.

Agreed ;-)

/Lennart

#7

Quote:

My first reaction as well, but it actually worked with quotes:

[lelle@...]$ db2 'select count(*) from price'

1
-----------
11765

:-)

Try:

db2 'select * from proj where deptno <> 'E21''

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

#8

Lennart Jonsson wrote:

Quote:

On 2010-05-21 02:07, Helmut Tessarek wrote:
You have to use double quotes in a UNIX shell as well.

My first reaction as well, but it actually worked with quotes:

[lelle@...]$ db2 'select count(*) from price'

The difference between single and double qoutes is that
within double quotes the shell with perform shell variable subsitution,
between single qoutes not.

echo "$HOSTNAME" ==> myhost
echo '$HOSTNAME' ==> $HOSTNAME

Quote:

1
-----------
11765

1 record(s) selected.

On 20.05.10 14:17 , Morch wrote:
[...]
Stupid Windoze.

Agreed ;-)

/Lennart

#9

Try db2 "SELECT * FROM "myTable""
;-)

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

#10

Good point.

[db2inst1@tessus2 ~]$ db2 "select * from "myTable""
SQL0204N "DB2INST1.MYTABLE" is an undefined name. SQLSTATE=42704
[db2inst1@tessus2 ~]$ db2 "select * from \"myTable\""

C1
-----------

0 record(s) selected.

But
db2 'select * from proj where deptno <> \'E21\''
does not work.

That is the reason why I never use single quotes...

On 21.05.10 2:56 , Serge Rielau wrote:

Quote:

Try db2 "SELECT * FROM "myTable""
;-)

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/