How to run cursor in snowflake?

Question

I have written below cursor in SQL and working file. But I am not able to run the same cursor on snowflake, please help.

DECLARE @CurrentMonth NVARCHAR(100)
DECLARE @CurrentMonth1 NVARCHAR(100)
DECLARE MYDateCURSOR CURSOR
DYNAMIC 
FOR
SELECT Collections_COE FROM [CollectionsAgeing_OTCN024_028_029]
OPEN MYDateCURSOR
FETCH LAST FROM MYDateCURSOR INTO @CurrentMonth
CLOSE MYDateCURSOR
DEALLOCATE MYDateCURSOR
--select  value from STRING_SPLIT(@CurrentMonth,'-') ;
select @CurrentMonth1=LEFT(@CurrentMonth,4)+cast(cast(RIGHT(@CurrentMonth,2) as int)-1 as varchar(2))
select Date, x1,y1,x1/y1 as DividedValue, round((x1/y1)-1,5) as Actual from(
SELECT  X.Past_Due_0 as x1,Y.Past_Due_0 as y1,X.Collections_COE as Date FROM [CollectionsAgeing_OTCN024_028_029] X
CROSS JOIN [CollectionsAgeing_OTCN024_028_029] Y
WHERE X.Collections_COE=@CurrentMonth and y.Collections_COE=@CurrentMonth1
)z

Hi Neeraj, you'll get a better response if you sort out the formatting of your code. When posting or editing a question you should see some guidance on the formatting on the right hand side, or else have a look in the help section — tomRedox

Mike Walton Mike Walton · Accepted Answer · 2019-10-21T16:04:37

Snowflake SQL does not support native SQL cursors. I believe you'll want to leverage a Javascript UDF, UDTF, or Stored Procedure (depending on what you want your output to be). Based on your example above, I believe a Javascript UDTF is what you need: https://docs.snowflake.net/manuals/sql-reference/udf-js-table-functions.html

How to run cursor in snowflake?

2 Answers