How to assign a variable to the result of a query in HANA?

[former_member445524]

Hello, all

How does one assign the result of a query to a scalar variable in HANA? In MS SQL I could:

SET @myvar = ( SELECT CardName FROM OCRD WHERE CardCode = @cc )

or, for several variables:

SELECT @var1 = Field1, @var2 = Field2 FROM MYTABLE WHERE Code = @code

The only way to do itin HANA that I have found is via a very long compass, i.e. by dint of a cursor. But cursors being intended for the iteration over several data rows, they are very clumsy in such simple an operation as a single assignment of a single scalar variable. I should say it is actually an abuse of cursor, so there ought to be a better way. Is there one?

[former_member445524 · Accepted solution]

Here is an example, ready for testing in Hana Studio:

DO
BEGIN
    DECLARE V1, v2 vARCHAR(8);
    SELECT 'Hello', 'there' INTO V1, V2 FROM DUMMY;
    SELECT :V1, :V2 FROM DUMMY;
END;

[lbreddemann · Accepted solution]

This is one of the many cases where just flipping through the documentation would’ve been the quicker way to get the answer.

[former_member445524 · Accepted solution]

Yes. But why the downvote? I saw my old unanswered question and answered it.

[dgrayson · Accepted solution]

lbreddemann But is it documented? If it's documented on the page for "SELECT Statement (Data Manipulation)" then I need new glasses, because I can't see it.

[lbreddemann · Accepted solution]

@Darren Grayson I suggest to keep the glasses (maybe clean them) and check the SQLScript documentation. Table variables are part on the scripting language but not of plain SQL.

[iashishsingh · Accepted solution]

Here is the link to SAP Help Doc for quick reference. I stumbled upon this post and then looked for the same in docs.

[former_member724275 · Accepted solution]

tnks so much!

[JULIAN21989 · Accepted solution]

Hi dear,

How can I execute it directly on SAP B1?, when I try the SAP return me this error:

[SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "BEGIN": line 7 col 1 (at pos 129)

I try with your example.

[former_member445524 · Accepted solution]

In B1, the code does not need the surrounding procedural block:

DECLARE V1, v2 vARCHAR(8);
SELECT 'Hello', 'there' INTO V1, V2 FROM DUMMY;
SELECT :V1, :V2 FROM DUMMY;

[JULIAN21989 · Accepted solution]

Thanks so much anton.shepelev3 !!