Skip to Content

Return result from SQL Bloc

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.

bloc1.jpg (36.1 kB)
bloc2.jpg (37.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Aug 18, 2018 at 05:16 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 18, 2018 at 05:19 AM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.