Apply Mapping of Key-Value pair in Snowflake SQL-CodePudding

I have this Table:

Table_1:
KeyValue, Month
 1,      Oct,
 3,      Nov,
 4,      Sep,
 5,      Jan

upto "December". I want this tale to use as dictionary for another table query. So, if i am giving a Case query, where i am getting the Month of a date than this month should be replaced by the KeyValue of Table_1. For example:

CAST(Month(getdate()) as int)

gives "Sep" but the output should be 
 "4" because it is KeyValues of 'Sep' in Table_1

How it can be achieved in Snowflake?

CodePudding user response：

I am trying to understand what is so complex here when you can achieve it with a simple join.

Consider month_mapping as your mapping table with key-value pair, and dataset as another table that you want to join with

with month_mapping (month_key, month_value) as
(
    select * from 
    (
        values (1, 'Oct')
                , (3, 'Nov')
                , (4, 'Sep')
                , (5, 'Jan')
    )
)
, dataset (actual_date) as 
(
    select * from
    values (current_timestamp)
                , (dateadd(month, 1, current_timestamp))
                , (dateadd(month, 2, current_timestamp))
                , (dateadd(month, 4, current_timestamp))
                , (dateadd(day, 2, current_timestamp))
                , (dateadd(day, -10, current_timestamp))
                , (dateadd(day, 20, current_timestamp))
)
select ds.actual_date, mm.month_key, mm.month_value
from dataset ds
inner join month_mapping mm
    on upper(mm.month_value) = upper(monthname(ds.actual_date))

CodePudding user response：

It is just a subquery that perfrom lookup in dictionary table:

SELECT *, (SELECT t1.KeyValue FROM Table_1 AS t1 WHERE t1.Month = MONTH(GETDATE()))
FROM some_tab;

Also possible to implement as JOIN:

SELECT s.*, t1.KeyValue
FROM some_tab AS s
JOIN Table_1 AS t1 
  ON t1.Month = s.<col_name>;