Snowflake SQL: why unexpected '<EOF>' when executing query in multi-statement script-CodePudding

I receive the error syntax error line 10 at position 20 unexpected '<EOF>'. (line 2) when I try to run the query below in Snowflake. Can someone please tell me how to fix this?

declare newline nvarchar(4);
set newline = char(13) || char(10);

select t.branchid,
    listagg(l.loanidnumber, :newline) within group(order by t.encompassid) as assoc_loan_nums,
    listagg(t.encompassid, :newline) within group(order by t.encompassid) as assoc_enc_ids
from tpo t
join loan l
on t.encompassid = l.encompassid
group by t.branchid;

CodePudding user response：

First of all I am adding a BEGIN and END-block to your code. This is resolving the first error.

However, then I am getting another error due to char(). Is this really what you want to do? CHAR is basically not converting to a character (string), but it converts a code point into the character that matches the input Unicode. If there is a wrong input, an error is thrown: https://docs.snowflake.com/en/sql-reference/functions/chr.html

With string concatenation basically below code works (if this is what you want to do). If not, please see this as a starting point.

declare newline nvarchar(4);
begin
set newline = concat('13', '10');

select t.branchid,
    listagg(l.loanidnumber, :newline) within group(order by t.encompassid) as assoc_loan_nums,
    listagg(t.encompassid, :newline) within group(order by t.encompassid) as assoc_enc_ids
from tpo t
join loan l
on t.encompassid = l.encompassid
group by t.branchid;
end;