Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Power BI services, connected to Snowflake, generates SQL that triggers an error: " Unable to connect to the data source ODBC: ERROR [42601] SQL compilation error: ambiguous column name 'name'."
The tables and attributes in question are DEALS.name, SCOPES.name, SUB_SCOPES.name, and QUESTIONS.name. They are related to each other from left to right in one to many relationships using "Manage Relationships".
select "C16",
{ fn convert(count(distinct("name")), SQL_DOUBLE) } + { fn convert(max("C1"), SQL_DOUBLE) } as "C1"
from
(
select "OTBL"."name",
"ITBL"."name" as "C16",
case
when "name" is null
then CAST(1 as INTEGER)
else CAST(0 as INTEGER)
end as "C1"
from
(
select "OTBL"."name",
"OTBL"."title",
"OTBL"."type",
"OTBL"."input_type",
"OTBL"."id",
"OTBL"."created_at",
"OTBL"."updated_at",
"OTBL"."sub_scope_id",
"OTBL"."discarded_at",
"OTBL"."publish_data_at",
"OTBL"."deal_id",
"OTBL"."C1",
"OTBL"."C2",
"OTBL"."C3",
"OTBL"."scope_id",
"OTBL"."C4",
"OTBL"."C5",
"OTBL"."C6",
"OTBL"."C7",
"ITBL"."name" as "C8",
"ITBL"."title" as "C9",
"ITBL"."id" as "C10",
"ITBL"."created_at" as "C11",
"ITBL"."updated_at" as "C12",
"ITBL"."deal_id" as "C13",
"ITBL"."discarded_at" as "C14",
"ITBL"."publish_data_at" as "C15"
from
(
select "OTBL"."name",
"OTBL"."title",
"OTBL"."type",
"OTBL"."input_type",
"OTBL"."id",
"OTBL"."created_at",
"OTBL"."updated_at",
"OTBL"."sub_scope_id",
"OTBL"."discarded_at",
"OTBL"."publish_data_at",
"OTBL"."deal_id",
"ITBL"."name" as "C1",
"ITBL"."title" as "C2",
"ITBL"."id" as "C3",
"ITBL"."scope_id",
"ITBL"."created_at" as "C4",
"ITBL"."updated_at" as "C5",
"ITBL"."discarded_at" as "C6",
"ITBL"."publish_data_at" as "C7"
from
(
select "name",
"title",
"type",
"input_type",
"id",
"created_at",
"updated_at",
"sub_scope_id",
"discarded_at",
"publish_data_at",
"deal_id"
from "STAGING"."PUBLIC"."QUESTIONS"
where "discarded_at" is null and not "publish_data_at" is null
) as "OTBL"
left outer join
(
select "name",
"title",
"id",
"scope_id",
"created_at",
"updated_at",
"discarded_at",
"publish_data_at"
from "STAGING"."PUBLIC"."SUB_SCOPES"
where "discarded_at" is null and not "publish_data_at" is null
) as "ITBL" on ("OTBL"."sub_scope_id" = "ITBL"."id")
) as "OTBL"
left outer join
(
select "name",
"title",
"id",
"created_at",
"updated_at",
"deal_id",
"discarded_at",
"publish_data_at"
from "STAGING"."PUBLIC"."SCOPES"
where "discarded_at" is null and not "publish_data_at" is null
) as "ITBL" on ("OTBL"."scope_id" = "ITBL"."id")
) as "OTBL"
left outer join
(
select "external_id",
"industry",
"sub_industry",
"location",
"name",
"vendor_id",
"client_contact",
"investment_thesis",
"id",
"created_at",
"updated_at",
"updated_from_vendor_at",
"scopes_protected_at",
"discarded_at",
"report_date",
"response_import_id",
"selected",
"vendor2_id",
"vendor_target_account_id",
"publish_data_at"
from "STAGING"."PUBLIC"."DEALS"
where "discarded_at" is null and not "publish_data_at" is null
) as "ITBL" on ("OTBL"."C13" = "ITBL"."id")
) as "ITBL"
group by "C16"
LIMIT 1000001 OFFSET 0
The expected behavior would be for Power BI to generate SQL that would allow you to have columns with the same name in each table and still perform joins.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.