Tore Halset wrote: > 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. > > -- two equal tables with different column order > create table dest_1 (USER_ID varchar(10), PRODUCT_ID varchar(8), > PERMIT_START_DATE timestamp, PERMIT_END_DATE timestamp); > create table dest_2 (PERMIT_END_DATE timestamp, PERMIT_START_DATE > timestamp, PRODUCT_ID varchar(8), USER_ID varchar(10)); > > -- ok > insert into dest_1 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; > -- same sql, but to table with different column order failes. > 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? Because the SQL standard says so. ISO/IEC 9075-2, Chapter 14.8, Syntax Rule 9: "If the <insert column list> is omitted, then an <insert column list> that identifies all columns of T in the ascending sequence of their ordinal positions within T is implicit." You want an explicit <insert column list>: INSERT INTO dest_2 (user_id, product_id, permit_start_date, permit_end_date) SELECT ... Yours, Laurenz Albe ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
