Need help below is the query , but table t3 is blank , which is giving me error in snowflake i.e unexpected 'SEP'.
select x48.SEP from
(
(
SELECT * FROM
(SELECT EID
, month11
, MIN(date_id) date_id
FROM t3 ---this table is empty
WHERE missing_mn >= 48
GROUP BY EID, month11
) PIVOT (MAX(date_id) FOR month11 IN ('SEP' SEP , 'OCT' OCT , 'NOV' NOV, 'DEC' DEC, 'JAN' JAN, 'FEB' FEB, 'MAR' MAR , 'APR' APR, 'MAY' MAY
)
)
)x48
)
T3 table with sameple data: EID = 1122345 ,month11 = SEP,MAR,APR etc .. , date_id = 20250101 or 20250201 (date to number )
The error you're getting has nothing to do with t3 being empty. You've got a syntax error in your pivot clause; by default, Snowflake encloses the pivoted column names with '
, and therefore, you have to put them in double quotes "
:
with t3(eid, month11, date_id, missing_mn) as (
select 2, 'SEP', '2025-09-14', 50 where 1=0
)
select x48."'SEP'"
from ((select
from (select EID,
month11,
min(date_id) date_id
from t3 ---this table is empty
where missing_mn >= 48
group by EID,
month11)
PIVOT(max(date_id)
for month11 in('SEP',
'OCT',
'NOV',
'DEC',
'JAN',
'FEB',
'MAR',
'APR',
'MAY'))) x48)
If you'd rather use aliases, you can define them like this:
with t3(eid, month11, date_id, missing_mn) as (
select 2, 'SEP', '2025-09-14', 50 where 1=0
)
select x48.sep
from ((select *
from (select EID,
month11,
min(date_id) date_id
from t3 ---this table is empty
where missing_mn >= 48
group by EID,
month11)
PIVOT(max(date_id)
for month11 in('SEP',
'OCT',
'NOV',
'DEC',
'JAN',
'FEB',
'MAR',
'APR',
'MAY')) as p(dat, sep, oct, nov, dec, jan, feb, mar, apr, may)) x48)
see also Snowflake Pivot Without Apostrophe