Snowflake Substring Concat Issue: Numeric value '' is not recognized

user_594801 0
Viewed 299

Have a fiscal quarter column, and I want to add a column that displays the previous quarter in the same row. I've done this successfully in tableau with logic like below.

IF RIGHT([current_qtr], 1) = "1"
THEN LEFT([current_qtr],2)+ STR(INT(MID([current_qtr], 3,2)) -1) +"Q4"
ELSE LEFT([current_qtr],5)+ STR(INT(RIGHT([current_qtr],1)) -1)
END

Example of expected output in snowflake

current_qtr previous_qtr other columns
FY21Q1 FY20Q4 etc
FY21Q2 FY21Q1 etc

When I try to do this in snowflake though I keep getting this "Numeric value '' is not recognized error instead. Any ideas how to fix? Below is what I attempted.

SELECT *,
CASE 
   WHEN SUBSTR(p.current_qtr,5,1) = 1 
      THEN SUBSTR(p.current_qtr,1,2) || TRY_CAST(SUBSTR(p.current_qtr,3,2) as INTEGER)-1 || 'Q4'
      ELSE SUBSTR(p.current_qtr,5,1) || TRY_CAST(SUBSTR(p.current_qtr,3,2) as INTEGER)-1
   End As prev_qtr
   FROM table as p
snowflake-cloud-data-platform sql
2 Answers

This logic might look way better as a SQL UDF than as a big pile of strange SQL inside a query:

create or replace function prev_quarter_formatted(x string)
returns string
as
$$
(
    select 'FY' || to_varchar(prev_q, '%y') || 'Q' || quarter(prev_q)
    from (
        select substr(x, 3,2) year
            , (1+3*(substr(x, 6,1)::int-1)) q_to_month
            , to_date(year||'-'||q_to_month||'-01', 'yy-mm-dd') as_date
            , dateadd(month, -3, as_date) prev_q
    )
)
$$
;


select prev_quarter_formatted('FY21Q1') a
;

Notice that for the logic I transformed the quarter into a date, subtracted 3 months, and formatted again.

user_1324380

There are some subtle differences in the syntax for casting. You can use your current CASE statement with some minor adjustments:

SELECT *,
CASE 
   WHEN SUBSTR(p.current_qtr,6,1) = '1' 
      THEN SUBSTR(p.current_qtr,1,2) || ((SUBSTR(p.current_qtr,3,2))::int - 1) || 'Q4'
      ELSE SUBSTR(p.current_qtr,1,2) || SUBSTR(p.current_qtr,3,2) || (SUBSTR(p.current_qtr,3,2)::int-1)
END AS prev_qtr
FROM table1 as p
user_127563810
Related