Currently am working on Snowflake stored procedure in which upsert operation has to be performed and for that wrote a merge Query which spans over multiple lines.
And result of both method, is same - query in single line
Now add this to the stored procedure
It is for better understanding and better readability but when I incorporated it in the stored procedure (Java script based)... it gave error.
JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in SPROC....
Related Articles
So it doesn't like multiline queries, thus I needed it to be in single line.
But there is no provision in snowflake worksheet to do it which makes my stored procedure manageable.
It can be done manually, but is there a better way - quick and less error prone.
Yes, with the help of notepad++
Let's take for example below multiline query--
merge into t1 using t2 on t1.t1key = t2.t2key
when matched and t2.marked = 1 then delete
when matched and t2.isnewstatus = 1 then update set val = t2.newval, status = t2.newstatus
when matched then update set val = t2.newval
when not matched then insert (val, status) values (t2.newval, t2.newstatus);
When you are writing query it's good to have multiline as it's more readable, so keep it that way.
Once you are done with query and satisfied then you can add the merge query in the SProc (highlighted in yellow)
Before adding, make it in single line , and to do that we take help of Notepad++ and there are two ways to Convert Multiple Lines into single line
1. Select the query --> go to Edit menu --> Line Operations --> Join Lines
2. Select the query
i. Open the Replace dialog ( Ctrl + H )
ii. Check the Wrap around option
iii. Choose the Regular expression search mode
iv. Fill in the regex (\h*\R)+ in the Find what section
v. Fill in the regex \x20 in the Replace with section
vi. Click on the Replace All button
And result of both method, is same - query in single line
If you know some other way, please feel free to share it in comments.
