OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42601] SQL compilation error: ambiguous column

[jacob-dickey]

I am using DirectQuery with Snowflake and for one of my visuals I am getting this error. It seems as though this is a bug with Power BI where it is not generating the SQL correctly. When I looked at the query it produced by DirectQuery in my Snowflake query history, I noticed the issue. Power BI had created two main sub-queries and joined them. After they were joined, there is a WHERE clause that did not have table qualifiers for the column names. The column it was erroring on existed in both sub-queries and Snowflake did not know how to handle that. 

Does anyone know a way around this or know if this has been reported elsewhere? I would think Power BI should be able to handle this report operation. 

Thanks,

[Anonymous]

I wanted to share to anyone else who views this thread that I got rid of the error message by changing the relationship between two tables in the model view. Once I checked "Assume referential integrity," I no longer got the error. Checking this box uses an INNER JOIN instead of an OUTER JOIN.

[themistoklis]

@jacob-dickey 

Best to send to us the query that you used.

This error "SQL compilation error: ambiguous column" is return in cases e.g. when you have a join of 2 tables and the same column appears in both of them. When you do a select statement and you dont specify the table this column belongs to then it throws an error.

You can specify the table through the alias or the table name itself

[jacob-dickey]

Here is the part of the query that is causing the trouble. Ran as is, it will error. I've noticed when I add qualifiers to the two "CREATED" columns in the WHERE clause at the very end, the query will work. Is there a way to tell Power BI to do this?

select "ITBL"."DIM_EQUIPMENT_KEY" as "C2",
            "ITBL"."MANUFACTURER",
            "ITBL"."CEID",
            "ITBL"."MODEL_NAME",
            "ITBL"."MODALITY",
            "OTBL"."TOTAL_COST"
        from 
        
        (
            select "OTBL"."DIM_EXPENSE_LINE_KEY",
                "OTBL"."EXPENSE_LINE_NUMBER",
                "OTBL"."EXPENSE_LINE_SYS_ID",
                "OTBL"."STATE",
                "OTBL"."CREATED_BY",
                "OTBL"."CREATED",
                "OTBL"."UPDATED_BY",
                "OTBL"."UPDATED",
                "OTBL"."EXPENSE_LINE_TYPE",
                "OTBL"."BILLBACK",
                "OTBL"."BILLABLE_REASON",
                "OTBL"."EFS_DURING",
                "OTBL"."EFS_AFTER",
                "OTBL"."START_TIME",
                "OTBL"."END_TIME",
                "OTBL"."EXTENDED_PRICE",
                "OTBL"."LABOR_HOURS",
                "OTBL"."QUANTITY_BILLED",
                "OTBL"."QUANTITY_SOLD",
                "OTBL"."TOTAL_COST", 
                "OTBL"."UNIT_PRICE",
                "OTBL"."TECHNICIAN_ID",
                "OTBL"."TECHNICIAN",
                "OTBL"."EXTENDED_COST",
                "OTBL"."TRIMEDX_LABOR_COST",
                "OTBL"."EXPENSE_LINE_COST",
                "OTBL"."LABOR_MINUTES",
                "OTBL"."AMOUNT",
                "OTBL"."CUSTOMER_INVOICE_NUMBER",
                "OTBL"."CUSTOMER_INVOICE_AMOUNT",
                "OTBL"."SHORT_DESCRIPTION",
                "OTBL"."SOURCE",
                "ITBL"."DIM_EXPENSE_LINE_KEY" as "C1",
                "ITBL"."DIM_WORK_ORDER_KEY",
                "ITBL"."DIM_CUSTOMER_KEY",
                "ITBL"."DIM_EQUIPMENT_KEY",
                "ITBL"."DIM_RECEIPT_LINE_KEY",
                "ITBL"."DIM_RECEIPT_KEY"
            from 
            (
                select "DIM_EXPENSE_LINE_KEY",
                    "EXPENSE_LINE_NUMBER",
                    "EXPENSE_LINE_SYS_ID",
                    "STATE",
                    "CREATED_BY",
                    "CREATED",
                    "UPDATED_BY",
                    "UPDATED",
                    "EXPENSE_LINE_TYPE",
                    "BILLBACK",
                    "BILLABLE_REASON",
                    "EFS_DURING",
                    "EFS_AFTER",
                    "START_TIME",
                    "END_TIME",
                    "EXTENDED_PRICE",
                    "LABOR_HOURS",
                    "QUANTITY_BILLED",
                    "QUANTITY_SOLD",
                    "TOTAL_COST",
                    "UNIT_PRICE",
                    "TECHNICIAN_ID",
                    "TECHNICIAN",
                    "EXTENDED_COST",
                    "TRIMEDX_LABOR_COST",
                    "EXPENSE_LINE_COST",
                    "LABOR_MINUTES",
                    "AMOUNT",
                    "CUSTOMER_INVOICE_NUMBER",
                    "CUSTOMER_INVOICE_AMOUNT",
                    "SHORT_DESCRIPTION",
                    "SOURCE"
                from "PRD"."REPORTING"."DIM_EXPENSE_LINE"
                where "BILLBACK" is not null
            ) as "OTBL"
            left outer join "PRD"."REPORTING"."FCT_EXPENSE_LINE" as "ITBL" on ("OTBL"."DIM_EXPENSE_LINE_KEY" = "ITBL"."DIM_EXPENSE_LINE_KEY")
        ) as "OTBL"
        
        left outer join 
        (
            select "DIM_EQUIPMENT_KEY",
                "EQUIPMENT_SYS_ID",
                "EQUIPMENT_NUMBER",
                "ASSET_TAG",
                "DEPARTMENT_SYS_ID",
                "DEPARTMENT",
                "DEPARTMENT_PRIMARY_CONTACT_ID",
                "DEPARTMENT_PRIMARY_CONTACT",
                "INSTALL_DATE",
                "VALID_INSTALL_DATE",
                "IP_ADDRESS",
                "VALID_IP_ADDRESS",
                "MAC_ADDRESS",
                "VALID_MAC_ADDRESS",
                "MANUFACTURER_ID",
                "MANUFACTURER",
                "LEGACY_MANUFACTURER_ID",
                "MANUFACTURER_CREATED",
                "EQUIPMENT_NAME",
                "SERIAL_NUMBER",
                "CREATED",
                "UPDATED",
                "ENTERED_DATE",
                "CEID",
                "OLD_CEID",
                "CONNECTED_TO_NETWORK",
                "DEVICE_STATUS",
                "SUB_STATUS",
                "DISPOSITION_DATE",
                "REACTIVATION_DATE",
                "MANUFACTURED_DATE",
                "LAST_PM_DATE",
                "NEXT_PM_DATE",
                "MISSION_CRITICAL",
                "EQUIPMENT_CRITICALITY",
                "PM_MANAGED",
                "RSQ_EQUIPID",
                "EQUIPMENT_COST",
                "WARRANTY_START_DATE",
                "WARRANTY_END_DATE",
                "WARRANTY_TYPE",
                "STRATEGY_SELECTION",
                "CURRENT_STRATEGY",
                "VENDOR_SITE_ID",
                "AE_TITLE",
                "DOES_DEVICE_HAVE_EPHI",
                "ALARMED",
                "RDM",
                "SITE_SPECIFIC_SVC",
                "MODEL_NUMBER",
                "MODEL_SYS_ID",
                "MODEL_NAME",
                "MODEL_ACTIVE",
                "EM_SCORE",
                "MODEL_CLASS",
                "MODEL_ID",
                "CAPABLE_OF_NETWORK_CONNECTION",
                "NO_LONGER_MANUFACTURED_DATE",
                "OEM_PART_SUPPORT_NOT_AVAILABLE_DATE",
                "OEM_PART_SUPPORT_NOT_AVAILABLE",
                "TRIMEDX_END_OF_SUPPORT_LIFE_DATE",
                "MODEL_CLASS_RISK",
                "PRICE_LEVEL",
                "MODEL_SVC",
                "MODEL_OS",
                "DESCRIPTION_SYS_ID",
                "DESCRIPTION_ACTIVE",
                "DESCRIPTION",
                "DESCRIPTION_ID",
                "MODALITY",
                "SERVICE_LINE",
                "MAJOR_CATEGORY",
                "MINOR_CATEGORY",
                "CAPITAL_TYPE",
                "COMMERCIAL_PACKAGE",
                "ESSENTIALS_COMMERCIAL_OFFERING_FLAG",
                "ADVANCED_COMMERCIAL_OFFERING_FLAG",
                "PRO_COMMERCIAL_OFFERING_FLAG",
                "OPERATING_SYSTEM",
                "OPERATING_SYSTEM_FAMILY",
                "OPERATING_SYSTEM_VERSION",
                "OPERATING_SYSTEM_EXTENDED_SUPPORT_END_DATE",
                "OPERATING_SYSTEM_MAINSTREAM_SUPPORT_END_DATE",
                "MODEL_TYPE_ID",
                "MODEL_TYPE",
                "PM_SCHEDULE",
                "PM_FREQUENCY_1",
                "PM_FREQUENCY_2",
                "PM_FREQUENCY_3",
                "PM_FREQUENCY_4",
                "PM_PROCEDURE_1",
                "PM_PROCEDURE_2",
                "PM_PROCEDURE_3",
                "PM_PROCEDURE_4",
                "RELATED_SERVICE_OFFERING",
                "CE_FLAG",
                "MME_FLAG",
                "URL",
                "LAST_TOUCH_DATE",
                "REGULATORY_ENVIRONMENT",
                "DEVICE_STORING_TRANSMITTING_DISPLAYING_PHI",
                "PHYSICAL_LOCATION",
                "PRIMARY_TECHNICIAN_ID",
                "PRIMARY_TECHNICIAN",
                "SECONDARY_TECHNICIAN_ID",
                "SECONDARY_TECHNICIAN",
                "PM_TECHNICIAN_ID",
                "PM_TECHNICIAN",
                "INSTALL_STATUS",
                "ASSIGNMENT_GROUP",
                "PM_MONTH",
                "CUSTOMER_DEVICE_EMR_ID",
                "DAYS_IN_SERVICE",
                "LIFETIME_TOTAL_COST",
                "PM_COUNT",
                "DATE_ADDED",
                case
                    when "MISSION_CRITICAL" = TRUE and not "MISSION_CRITICAL" is null
                    then CAST(1 as INTEGER)
                    else CAST(0 as INTEGER)
                end as "C1",
                case
                    when "MISSION_CRITICAL" = TRUE and not "MISSION_CRITICAL" is null
                    then CAST(5 as INTEGER)
                    else CAST(10 as INTEGER)
                end as "C2",
                { fn concat('A', "EQUIPMENT_SYS_ID") } as "C3"
            from "PRD"."REPORTING"."DIM_EQUIPMENT"
            where { fn convert("CE_FLAG", SQL_DOUBLE) } = CAST(1 as DOUBLE) and not "CE_FLAG" is null
        ) as "ITBL" 
        
        on ("OTBL"."DIM_EQUIPMENT_KEY" = "ITBL"."DIM_EQUIPMENT_KEY")
        

        where (((((("CE_FLAG" = CAST(1 as DECIMAL) and "CREATED" < CAST('2020-10-03 00:00:00.00' as TIMESTAMP)) and "CREATED" >= CAST('2020-06-05 00:00:00.00' as TIMESTAMP)) and (not "DEVICE_STATUS" = 'Archived' or "DEVICE_STATUS" is null)) and (not "DEVICE_STATUS" = 'Disposition' or "DEVICE_STATUS" is null)) and not "MODALITY" is null) and (not "MODEL_NAME" = 'UNMATCHED' or "MODEL_NAME" is null)) and not "DESCRIPTION" is null
    

[themistoklis]

Hello @jacob-dickey 

Do you actually mean whether PowerBI can add the right aliases right next to field names?

This cannot be done by PowerBI.

To give you some context.

The query that you wrote will also through the same error message if you run it to any database client. It is database generated error and not PowerBI one. In other words the error says that you didnt write the query properly and you need to make the necessary amendments.

On your query you use 2 tables ITBL and OTBL. For all fields you have used an alias but not on the fields in the where statement. This is bad writing of a SQL Query.

Also Created field is one of the main fields in a database table. If you dont specify the table alias correctly it will return wrong data.

The solution in your case is to put an alias (ITBL or OTBL) in front of every field in the where statement.

I hope that helped

[jacob-dickey]

Correct, I agree that is the problem with the query. However, I did not write that query, Power BI DirectQuery did. 
I found that query in my Snowflake query history after getting the error in Power BI. 

[themistoklis]

@jacob-dickey

If i remember correctly you can not write a custom query and send it through PowerBI to snowflake.

You can only connect directly to tables in Snowflake. Corrct me if im wrong.

My understanding is that you have multiple steps in Power Query editor and one of the steps joins 2 tables. Is this right?

If this is the case, i think the best way to overcome this issue is by adding one more step and delete the duplicate columns from the table you are not interested in. I hope tis makes sense??

[jacob-dickey]

Correct, from what I can understand, you cannot send custom queries through Power BI. You can only bring in tables and join them by creating relationships in the data model. This is how I joined the tables that appear in my query above. 
I agree, I think the problem will be solved if one of the "CREATED" columns was removed from the data model so that only one would appear in the DirectQuery result. However, I am using both "CREATED" columns in various parts of the report. 
Do you think currently the only option would be to edit the base table in Snowflake to rename the column? If so, I would consider this error to be a bug for Power BI. I would think it should be able to handle this kind of report operation. 

[themistoklis · Accepted solution]

In this case the best (and preferred option) in my opition is to create a view in snowflake.

Inside the view rename the field. The connect PowerBi to View instead of Table

Do not rename the field directly in the table. I wouldnt recommend that

[jacob-dickey]

I agree, unfortunately, I think this might be the only viable solution right now. I wish there was a better way since this will cause some rework, but this should still work. Thank you for your help!