"$" symbol before a field in SQL-CodePudding

This is from a query I found in a company GitHub repo. We use Snowflake.

I was wondering what the $ before a field in SQL does. I changed the names of the fields and table for this post. And in this table, there is no field called business_date. The table has other date and timestamp fields, though. Is $ being used to declare a variable?

select
        $business_date as business_date
        , t.type
        , t.platform
        , t.country
    from table as t

CodePudding user response：

That identifies a SQL/Session Variable (SQL Variables)

In your example, the $business_date is a value, not a field.

So what is happening is $business_date is being defined outside of this statement for the session. The select statement above would return the value of the variable for every row of that query.

So there is one more command being issued prior to this statement.

-- set variable 
set business_date = '2022-04-16';

-- issue query
select
        $business_date as business_date
        , t.type
        , t.platform
        , t.country
    from table as t

the $ sign is the prefix used to identify variables in SQL statements. As @Simeon Pilgrim pointed out if you need to dynamically set the name of an object you must use the identifier function

select * from identifier($my_table_name);

CodePudding user response：

It is a Session Variable or SQL Variables

so you can do things like:

set schema_name = 'my_db.my_schema';

use schema identifier($schema_name);