Is there a way to shorthand this CASE statement in Snowflake/SQL?-CodePudding

Have this case statement here that I want to use with both "CASE WHEN" and CASE ILIKE" similar to below:

SELECT
  CASE coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column)
     when 'green' then 'GO'
     when 'red' then 'STOP'
     when 'yellow' then 'SLOW'
     else
         case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%green' then 'GO?'
         case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%yellow' then 'SLOW?'
         case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%red' then 'STOP?'     
   END as sample_derived_column
FROM SAMPLE_TABLE_NAME;

Is there a way to write the (NESTED) ilike case statements similar to the 'Shorthand' being used in the 'CASE WHEN' statements at the top of the code block?

Thanks in advance!

Sample data here:

random_sample_column
--------------
yellow30-1123$%schmidt
jingle43123heimer
red
isthelightgreen
beluw
beow
blue

CodePudding user response：

One possible way is to use column aliases:

WITH cte AS (
SELECT *,
  coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column) AS helper_col1,
  coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ') AS helper_col2,
  CASE helper_col1
     when 'green' then 'GO'
     when 'red' then 'STOP'
     when 'yellow' then 'SLOW'
     else
         case when helper_col2 ilike '%green' then 'GO?'
              when helper_col2 ilike '%yellow' then 'SLOW?'
              when helper_col2 ilike '%red' then 'STOP?' 
         end    
   END as sample_derived_column
FROM SAMPLE_TABLE_NAME
)
SELECT * EXCLUDE (helper_col1, helper_col2)
FROM cte;

Nesting is not readable so:

WITH cte AS (
SELECT *,
  coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column) AS helper_col1,
  coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ') AS helper_col2,
  CASE
     when helper_col1 =  'green'      then 'GO'
     when helper_col1 = 'red'         then 'STOP'
     when helper_col1 = 'yellow'      then 'SLOW'
     when helper_col2 ilike '%green'  then 'GO?'
     when helper_col2 ilike '%yellow' then 'SLOW?'
     when helper_col2 ilike '%red'    then 'STOP?'       
   END as sample_derived_column
FROM SAMPLE_TABLE_NAME
)
SELECT * EXCLUDE (helper_col1, helper_col2)
FROM cte;

EDIT:

In order to perform the simple case expression with LIKE predicate and wildcard:

SELECT 
   CASE expresssion
     WHEN ILIKE '%green' THEN 'GO?'
     WHEN ILIKE '%yellow' THEN 'SLOW?'
     WHEN ILIKE '%red' THEN 'STOP?'
   END 
FROM SAMPLE_TABLE_NAME;

the used SQL dialect has to implement optional SQL:2003 Extended CASE expression(F262) feature.

More at: SQL 'CASE WHEN x' vs. 'CASE x WHEN' with greater-than condition?