CODEWITHSUNDEEP
snowflake-cloud-data-platformalias
Dinakar Ullas
Dinakar Ullas

Reputation: 325

Snowflake TABLE(RESULT_SCAN(LAST_QUERY_ID()))

After executing a query on a table, there is a need to use the result from TABLE(RESULT_SCAN(LAST_QUERY_ID())). But we are facing a complication just because of column names being in lower case from TABLE(RESULT_SCAN(LAST_QUERY_ID())). is aliasing the only option to have the column names in upper case? we want to avoid aliasing, because the double quoting around column names is increasing the complexity of the logic.

Upvotes: 1

Views: 11952

Answers (1)

Eric Lin
Eric Lin

Reputation: 1510

The case of column names from the resultset depends on the case was used in the previous query.

Remember that Snowflake will treat everything as UPPERCASE unless double quoted. So below will return the same column name MY_COLUMN:

SELECT my_column FROM test;
SELECT MY_column FROM test;
SELECT MY_COLUMN FROM test;

However, if you double quote the column name, then they will be preserved:

SELECT MY_column as "my_column" FROM test;

So above will force the returned column to be in the lower case.

See test below:

create or replace table test (my_column int);
insert into test values (1);

select my_column from test;
SELECT my_column from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-----------+
| MY_COLUMN |
|-----------|
|         1 |
+-----------+

SELECT MY_column FROM test;
SELECT my_column from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-----------+
| MY_COLUMN |
|-----------|
|         1 |
+-----------+

SELECT MY_COLUMN FROM test;
SELECT my_column from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-----------+
| MY_COLUMN |
|-----------|
|         1 |
+-----------+

SELECT MY_column as "my_column" FROM test;
SELECT my_column from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'MY_COLUMN'

SELECT MY_column as "my_column" FROM test;
SELECT MY_COLUMN from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'MY_COLUMN'

SELECT MY_column as "my_column" FROM test;
SELECT "my_column" from TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-----------+
| my_column |
|-----------|
|         1 |
+-----------+

So, if I understand your question correctly, to avoid aliasing in the query against RESULT_SCAN, you will need to ensure the column names from the previous query match what are you after. Otherwise, you will have to add alias to force to use different case.

Upvotes: 3

Related Questions