Hi,

I'm working on DB2 v7.2 on windows 2000 server.
I'm trying to run a sql -

insert into table tab1 (col1, col2, col3) values (Select col1, col2, col3 from tab2)

which is not working. Gives the error
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0412N Multiple columns are returned from a subquery that is allowed only
one column. SQLSTATE=42823.

Stragely though, if I give a sql

insert into table tab1 (col1, col2, col3) (Select a1, a2, a3 from tab2)

It works perfectly.

When looking at the syntax rules, the first sql is right and this one is wrong.
I dont know why it is behaving in this way.
Please let me know if you've any comments on this.

Thanks,
-Preeti

Actually, according to the syntax rules, the first one is wrong and the second one is right.

Here is the syntax:

>>-INSERT INTO--+-table-name-------+---------------------------->
+-view-name--------+
+-nickname---------+

>--+-----------------------+------------->
| .-,-----------. |
| V | |
'-(----column-name-+--)-'

.-,----------------------------.
V |
>--+-VALUES----+-+-expression-+-----------+-+----------+--------><
| | +-NULL-------+ | |
| | '-DEFAULT----' | |
| | .-,--------------. | |
| | V | | |
| '-(----+-expression-+-+--)-' |
| +-NULL-------+ |
| '-DEFAULT----' |
'-+-----------------------------------+--fullselect-'
| .-,-----------------------. |
| V | |
'-WITH----common-table-expression-+-'


The reason the first one is wrong is that you have one expression (the fullselect) returning more than one colunm. You would need something like this:

Insert into table tab1 (col1, col2, col3) values ((Select col1 from tab2),(select col2 from tab2),(select col3 from tab2))

The reason the second one is correct is that it follows the
INSERT INTO tabname (columns, ...) fullselect

HTH

Andy

Thanks a lot. That clears my confusion.