Tore Halset wrote:
> Hello.
>
> One of our users tried a "insert into ... select ..." that gave a
> strange error message. After digging into the issue, the problem seem to
> be that the order of the columns in the select statement must match the
> table definition. Here is a way to reproduce this case.
> insert into dest_2 select USER_ID, PRODUCT_ID, min(PERMIT_START_DATE) as
> PERMIT_START_DATE, max(PERMIT_END_DATE) as PERMIT_END_DATE from source
> group by USER_ID, PRODUCT_ID;
>
> Why does the column order matter when the subselect has all the correct
> column names?
The names do not matter - the database won't try to match up the names.
Think about it in comparison with INSERT ... VALUES - it's the same layout.
What you need to do is supply the column-names for the insert (this is a
good idea anyway - it makes it explicit what is going on and will cope
better if you change the definition of dest_2).
INSERT INTO dest_2 (permit_end_date, permit_start_date, ...)
SELECT <column for permit_end_date>, <column for permit_start_date>, ...
--
Richard Huxton
Archonet Ltd