CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformcommon-table-expression
Ezer K
Ezer K

Reputation: 3739

show columns in CTE returns an error - why?

I have a show columns query that works fine:

SHOW COLUMNS IN table

but it fails when trying to put it in a CTE, like this:

WITH columns_table AS (
SHOW COLUMNS IN table
)

SELECT * from columns_table

any ideas why and how to fix it?

Upvotes: 0

Views: 499

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Using RESULT_SCAN:

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table. This is particularly useful if you want to process the output from any of the following:

SHOW or DESC[RIBE] command that you executed.

SHOW COLUMNS IN ...;

WITH columns_table AS (
   SELECT *
   FROM table(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT *
FROM columns_table;

Upvotes: 2

Anand
Anand

Reputation: 564

CTE requires select clause and we cannot use SHOW COLUMN IN CTE's and as a alterative use INFORMATION_SCHEMA to retrieve metadata .Like below:

WITH columns_table AS (
Select * from INTL_DB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='CURRENCIES'
)
SELECT * from columns_table;

Upvotes: 1

Related Questions