cancel
Showing results for 
Search instead for 
Did you mean: 

Syntax for COALESCE

0 Kudos

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

Accepted Solutions (0)

Answers (4)

Answers (4)

0 Kudos

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)

former_member182302
Active Contributor
0 Kudos

Are you trying to create the procedure or using SQL Script or just executing in SQL console?

Regards,

Krishna Tangudu

pfefferf
Active Contributor
0 Kudos

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

rindia
Active Contributor
0 Kudos

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 ************/

0 Kudos

Hi Raj Kumar,

Have you Executed the above query as Procedure..??

Because when I'm trying to execute I'm getting some errors.

Could you please share the complete query you have successfully executed.

lucas_oliveira
Advisor
Advisor
0 Kudos

Everyone here is trying to guess which error you're facing and you are still not providing the actual error. Why not provide the complete error message with the code you defined and save us the guessing time ?

BRs,

Lucas de Oliveira

rindia
Active Contributor
0 Kudos

I tried this as SQLScript CV:

The procedure would be:

Regards

Raj

chandan_praharaj
Contributor
0 Kudos

What is the error you are getting?  As Florian mentioned , are you trying to write SQLScript for this kindl of logic? Please elaborate.

pfefferf
Active Contributor
0 Kudos

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