Return result from SQL Bloc

[former_member543290]

bloc1.jpgbloc2.jpg

Hello,

I have the following example in Hana Studio, where I would like to assign a value to a variable and then display it with an sql query. I have tried the following:

DO BEGIN

DECLARE PALLET_UOFM varchar(100);

SET 'PALLET_UOFM' = '1234';

SELECT :PALLET_UOFM AS PRINTED_PALLET_UOFM FROM DUMMY;

END;

I've also tried the following:

DO BEGIN

DECLARE PALLET_UOFM varchar(100);

SET 'PALLET_UOFM' = '1234';

SELECT PALLET_UOFM AS PRINTED_PALLET_UOFM FROM DUMMY;

END;

In both cases, it prints null. Please see tthe attachements.

Is there something missing ?

Thanks for your help.

[pfefferf · Accepted solution]

With the SET command you do not set the value of the local defined variable PALLET_UOFM. SET sets a session context variable, which can be read with function SESSION_CONTEXT. A local variable declared with DECLARE is set by a "normal" assignment like "PALLET_UOFM := 'my value'".

Check the following little example which should show you the difference:

do
begin
  -- local variable value
  declare local_variable nvarchar(100) := 'local variable value';
  -- assign value to local variable (alternativ here): local_variable := 'local variable value';
    
  -- set session variable
  set 'my_session_variable' = 'session variable value';
  
  -- query
  select 
    session_context('my_session_variable') as value_from_session_variable, -- get session variable value
    :local_variable as value_from_local_variable -- get local variable value
    from dummy;
end;

Regards,
Florian

[lbreddemann]

The SQLScript reference has the answer to this. The syntax is called SELECT ... INTO.

Also, when using the variables you need to use the colon-notation.

SELECT PALLET_UOFM AS PRINTED_PALLET_UOFM FROM DUMMY; 

is wrong.

SELECT :PALLET_UOFM AS PRINTED_PALLET_UOFM FROM DUMMY;

is correct.

All that is in the documentation - highly recommended reading!

[lbreddemann]

nevermind - I didn't read the question properly. Irony not being lost on me here...