Why is my column alias not working in subquery?

Question

I am trying to run below code on Snowflake:

SELECT 
agg_zone_delivery_metrics_hourly.zone_code,
agg_zone_delivery_metrics_hourly.start_of_period_local_date,
MIN(CASE WHEN azdmh.start_of_period_local_hour_of_day = '18' THEN agg_zone_delivery_metrics_hourly.throughput_actual END) AS TP18,
MIN(CASE WHEN azdmh.start_of_period_local_hour_of_day = '19' THEN agg_zone_delivery_metrics_hourly.throughput_actual END) AS TP19,
MIN(CASE WHEN azdmh.start_of_period_local_hour_of_day = '20' THEN agg_zone_delivery_metrics_hourly.throughput_actual END) AS TP20
FROM (
SELECT 
    CAST(EXTRACT(HOUR FROM CAST(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL  AS TIMESTAMP)) AS INT) AS "azdmh.start_of_period_local_hour_of_day",
    TO_CHAR(TO_DATE(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL ), 'YYYY-MM-DD') AS "agg_zone_delivery_metrics_hourly.start_of_period_local_date",
    agg_zone_delivery_metrics_hourly.ZONE_CODE  AS "agg_zone_delivery_metrics_hourly.zone_code",
    nullif(sum(agg_zone_delivery_metrics_hourly.orders_delivered), 0) / nullif(sum(agg_zone_delivery_metrics_hourly.rider_hours_worked_dhw_sum), 0)
       AS "agg_zone_delivery_metrics_hourly.throughput_actual"
FROM tblB  AS agg_zone_delivery_metrics_hourly

WHERE ((((agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL ) >= ((DATEADD('day', -7, DATE_TRUNC('week', DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'Hongkong', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))))) AND (agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL ) < ((DATEADD('day', 7, DATEADD('day', -7, DATE_TRUNC('week', DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'Hongkong', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))))))))) AND (((CASE TO_CHAR(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL , 'DY')
WHEN 'Tue' THEN 'Tuesday'
WHEN 'Wed' THEN 'Wednesday'
WHEN 'Thu' THEN 'Thursday'
WHEN 'Sat' THEN 'Saturday'
ELSE TO_CHAR(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL , 'DY') || 'day' END) = 'Sunday')) AND (CAST(EXTRACT(HOUR FROM CAST(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL  AS TIMESTAMP)) AS INT) IN (20,18,19)) AND (agg_zone_delivery_metrics_hourly.COUNTRY_NAME = 'Hong Kong') AND agg_zone_delivery_metrics_hourly.is_within_zone_hours AND (agg_zone_delivery_metrics_hourly.COUNTRY_NAME = 'Hong Kong')
GROUP BY 1,TO_DATE(agg_zone_delivery_metrics_hourly.START_OF_PERIOD_LOCAL ),3) AS tblA

However, I am receiving below error:

SQL compilation error: error line 2 at position 0 invalid identifier 'AGG_ZONE_DELIVERY_METRICS_HOURLY.ZONE_CODE'

I believe the issue here is that the code is not able to recognise the column alias from subquery.

You need to double quote those strange column names. Btw, your formatting is a mess — dnoeth, Dec 15 '18 at 17:31

score 0 · Answer 1 · answered Dec 15 '18 at 17:30

If you enclose an alias in ", as you do for the columns of the inner query, you have to enclose any reference in " too and use the exact name (case sensitive).

Try

SELECT 
"agg_zone_delivery_metrics_hourly.zone_code",
...

(and do the same for the other columns).

Or remove the " around the aliases and use aliases without a ., which is confusing anyway.

GMB · Accepted Answer · 2018-12-15T18:12:10.130

This is related to the table aliases that you are using. This kind of alias :

AS "agg_zone_delivery_metrics_hourly.throughput_actual"

is confusing, because :

if you use double quotes « " » in the table alias, you have to use them in every expression that refers to this alias
the dot (« . ») is typically used to separate table aliases from column names, like « table.column »

The shorter solution would be to enclose all references to these weird aliases with double quotes as well.

However, for the sake of readability and maintenance, it would be far better to change your table aliases, by removing double quotes and replacing the dot with another character (underscore is commonly used). The above could be changed to :

AS agg_zone_delivery_metrics_hourly_throughput_actual

PS1 : you should consider improving the formatting of your query, which, as it is, is very difficult to read.

PS2 : there are other issues with your query. Namely, you have aggregated functions in your top query (« SUM... ») but no « GROUP BY » clause : this won’t work. You need to add a GROUP BY clause at the end of the query, that refers to the non-aggregated columns in your output, something like :

GROUP BY
    agg_zone_delivery_metrics_hourly.zone_code,
    agg_zone_delivery_metrics_hourly.start_of_period_local_date,

Thanks for the suggestions. I removed the quotes and simplified the alias names. However, I am getting the below error now: 'SQL compilation error: [TBLA.ZONE_CODE] is not a valid group by expression' — Tushar Agarwal, Dec 15 '18 at 17:58
Yes @TusharAgarwal, now the aliasing issue is fixed and you are running into other problems... just updated my answer on the GROUP BY issue — GMB, Dec 15 '18 at 18:13

Why is my column alias not working in subquery?

2 Answers2