My query is failing with this error: `invalid column name: OUT_OUT.STORE__ID: line 30 col 10 (at pos 939)`
-- root_node()
SELECT TOP 10000 r.brand__id AS "brand__id",
dim_brand.name AS "brand",
r.amount AS "amount"
FROM
(
-- leaf_node(out, fact_sales)
SELECT dim_product.brand_id AS brand__id,
SUM(fact_sales.sales_amt) AS amount
FROM fact_sales
JOIN dim_product
ON dim_product.product_id = fact_sales.product_id
JOIN dim_store
ON dim_store.store_id = fact_sales.store_id
LEFT OUTER JOIN
(
-- qualifier_node(out_out)
SELECT r.store__id AS "store__id" -- the query works if I write AS "STORE__ID"
FROM
(
-- leaf_node(out_out, dim_store)
SELECT dim_store.store_id AS store__id
FROM dim_store
JOIN dim_region
ON dim_region.region_id = dim_store.region_id
AND (LOWER(dim_region.name) = 'southeast')
) AS r
) AS out_out
ON out_out.store__id = dim_store.store_id
WHERE (out_out.store__id IS NOT NULL)
GROUP BY dim_product.brand_id
) AS r
JOIN dim_brand
ON dim_brand.brand_id = r.brand__id
ORDER BY r.amount DESC
I can work around the problem if I change the alias to "STORE__ID"; i.e. uppercase.
But lowercase columns seem to work everywhere else. Is it just the join condition that's getting confused by the lowercase column?
Thanks.
-Larry
