on 05-09-2016 1:48 PM
Dear All,
I'm getting an error while using the COALESCE in SAP HANA Studio. Here i will attach my query for concatenating Warehouse Code from Warehouse Master.
WHSList varchar(5000);
SELECT COALESCE(:WHSList || ',', '') || '[' || "WhsCode" || ']' INTO WHSList FROM OWHS;
Please Help on this
Hi All,
Sorry Everyone.
Here is the Error message I'm getting while executing the Syntax:-
Could not execute 'DECLARE WHSList varchar(5000)'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "DECLARE": line 1 col 1 (at pos 1)
Could not execute 'SELECT COALESCE(:WHSList || ',', '') || '"' || "WhsCode" || '"' INTO WHSList FROM SBODEMOIN."OWHS"'
SAP DBTech JDBC: [467]: cannot use parameter variable: WHSLIST: line 1 col 17 (at pos 16)
Could not execute 'SELECT WHSList FROM DUMMY'
SAP DBTech JDBC: [260]: invalid column name: WHSLIST: line 1 col 8 (at pos 7)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Vijeesh,
you are getting the error messages, because you are trying to execute SQLScript code within the SQL console. That is not possible in a direct way. SQLScript coding has to be implemented in a procedure or a function.
If you have a SPS10 system (or higher) you can use for testing reasons anonymous blocks in the SQL console to execute SQLScript coding. For instance (I changed your coding to use the DUMMY column of the DUMMY table; but of course it works also with your artifacts in case they exist):
do
begin
DECLARE WHSList varchar(5000);
SELECT COALESCE(:WHSList || ',', '') || '"' || "DUMMY" || '"' INTO WHSList FROM DUMMY;
select :WHSList from DUMMY;
end;
Regards,
Florian
I tried this in SQLScript cv.
Just added DECLARE for the variable and getting no errors.
Also it is better to use default value while declaring variables.
EX: DECLARE WHSList varchar(5000) DEFAULT '';
BEGIN
DECLARE WHSList varchar(5000);
SELECT COALESCE(:WHSList || ',', '') || '[' || WhsCode || ']'
INTO WHSList FROM RSALLA.OWHS;
VAR_OUT = SELECT WHSList AS WHSLIST FROM DUMMY;
END /********* End Procedure Script ************/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What is the error you are getting? As Florian mentioned , are you trying to write SQLScript for this kindl of logic? Please elaborate.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Vijeesh,
where do you execute the coding? In a SQL console, as anonymous block in an SQL console, calling a procedure/function? What error do you get?
If you doing this as SQLScript, than the "declare" keyword is missing for the declaration of your WHSList variable.
Regards,
Florian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.