duplicate column name which is not

[Yulin Wen]

I am using the latest ucanaccess 3.0 jdbc driver and here is one error I've seen that doesn't make sense.

first i run the create table as statement without issue:

CREATE TABLE [EBTUKQSHM] AS (
SELECT
[t0].[ProductName] AS [ProductName],
[t1].[ContactName] AS [CustomerCon],
sum(([t2].[UnitPrice] * [t2].[Quantity])) AS [Revenue],
count([t2].[OrderID]) AS [OrderCount]
FROM
[order_details] [t2]
INNER JOIN [products] [t0] ON (([t2].[ProductID] = [t0].[ProductID]))
INNER JOIN [orders] [t3] ON (([t2].[OrderID] = [t3].[OrderID]))
INNER JOIN [customers] [t1] ON (([t3].[CustomerID] = [t1].[CustomerID]))
GROUP BY
[t0].[ProductName],
[t1].[ContactName]
) WITH DATA

then I run the 2nd:

CREATE TABLE [QBOSHT7Z9FJ0] AS (
SELECT
COALESCE([t0].[ProductName], [t1].[ProductName]) AS [ProductName],
[t0].[CustomerCon] AS [CustomerCon],
[t0].[Revenue] AS [Revenue],
[t0].[OrderCount] AS [OrderCount],
[t1].[Revenue] AS [TotalProduc]
FROM
[EBTUKQSHM] [t0]
LEFT JOIN [QBU67BTT6] [t1] ON (([t1].[ProductName] = [t0].[ProductName]))
) WITH DATA

this one gave me the following error:

An error occurred when executing the SQL command:
CREATE TABLE [QBOSHT7Z9FJ0] AS (
SELECT
COALESCE([t0].[ProductName], [t1].[ProductName]) AS [ProductName],
[t0].[CustomerCon] AS [Custo...

UCAExc:::3.0.0 duplicate column name: TOTALPRODUC
Execution time: 0.13s
1 statement failed.

The column name TOTALPRODUC is not duplicate. Even if I change it to anything else it gave me the same duplicate column name error.

Thanks.
Yulin

[Marco Amadei]

You're right, it's a trivial bug remapping the same column name (from different tables) into different aliases. It will be fixed asap in svn, and it will be in the 3.0.1. It may happen in
create table ... as select command only, so in the meantime you can use a normal create table statement + insert into .... select.

[Yulin Wen]

Thank you. I will use the alternative solution for now.