Cannot rename column neither exclude column in snowflake sql select query

by

Below query is working great

Example 1

SELECT
* EXCLUDE company_name
FROM employee

Example 2

SELECT 
* RENAME id as emp_id
FROM employee

But when I am trying to select with table alias name then it is not working and getting SQL error, I want to use alias for some joins and I am having ambiguous column error on column company_name

Example 1

SELECT
* EXCLUDE t1.company_name 
FROM employee t1

Example 2

SELECT 
* RENAME t1.id as emp_id
FROM employee t1

I followed the Snowflake Docs but not found anything related

SQL COMPILATION ERROR: syntax error at line 1 at position 18 unexpected ‘.’.

  • 1

    Please update your question with the error that you’re getting

    – 

  • 1

    HI , the error “ambiguous column error on column” means a column with the same name is present in more than one table, you need to add an alias or table name to the column, please show the SQL that is being used.

    – 

The documentation says you cannot use qualified column names in either exclude or rename.

If you are selecting from multiple tables, use SELECT table_name.* to
specify that you want to select all columns from a specific table, and
specify the unqualified column name in EXCLUDE. For example:

SELECT table_a.* EXCLUDE column_in_table_a , table_b.* EXCLUDE
column_in_table_b …

So change what you have to something like this

SELECT t1.* EXCLUDE company_name, t2.* EXCLUDE employee_id
FROM employee t1
JOIN salary t2 on t1.employee_id = t2.employee_id

Leave a Comment