null-columns in select-statement

["Kasper, Kay" <ka...@ckc.de>]

Hi,

i try to use a select-statement like this

"(select col1, col2, null, col4 from tablex)"

in an insert-statement.

As a result i get an SQLException with state 42X01, code 30000 that says
syntax error at null.

Even if i use a very simple statement like (i created a table like DUAL in
Oracle)

"select 'x', null from dual"

i get the same message.

I tried null-columns in select-statements with UNION ALL, which is very
common, i get the syntax error.

Whats wrong? In my opinion this should work without any problems.


Thanks for your help.

Kay



--------------------------
ckc ag
Sitz:
Industriestr. 10
38110 Braunschweig

Telefon 05307 / 80 20 0
Telefax 05307 / 80 20 444
http://www.ckc.de

Amtsgericht Braunschweig
HRB 5405

Vorstand:
H.-G. Christian Krentel
(Vorsitzender)

Aufsichtsrat:
Dr. Heinz-Werner Weinrich
(Vorsitzender)
Hans Bodenstein
Dr. Rita Schulz
--------------------------

Re: null-columns in select-statement

[Mark Thornton <mt...@optrak.co.uk>]

Kasper, Kay wrote:
> Hi,
>
> i try to use a select-statement like this
>
> "(select col1, col2, null, col4 from tablex)"
>
> in an insert-statement.
>
> As a result i get an SQLException with state 42X01, code 30000 that says
> syntax error at null.
>
> Even if i use a very simple statement like (i created a table like DUAL in
> Oracle)
>
> "select 'x', null from dual"
>
> i get the same message.
>
> I tried null-columns in select-statements with UNION ALL, which is very
> common, i get the syntax error.
>
> Whats wrong? In my opinion this should work without any problems.
>
>
> Thanks for your help.
>
> Kay
>
>   
Standard SQL-92 doesn't appear to allow NULL in a select list. It can 
appear in a row value list, or as a case result, either of which might 
provide an alternative way of achieving the required outcome.

Mark Thornton