How to create a table variable for using in a JOIN?

Question

Because SAP HANA doesn't support CTEs, I'd like to learn to use table variables in joins.

I've found simple examples of using table variables in a join in SQL Server, like below: https://www.youtube.com/watch?v=2fc6YUCQSV8

I have not found any simple examples of creating table variables in SAP HANA, let alone using them in a join.

Does anyone have a simple example of how to create a table variable and use it in a join in SAP HANA?

Thanks!

@SandraRossi to separate subqueries into distinct chunks to be joined later, in order to improve readability of long queries. — David Mays, Nov 24 '22 at 10:13
I deleted my question, as you were just asking an example how to use tables variables e.g. SELECT ... FROM :tab1 ... my point was just that if you know how to do a simple query on a table variable, then there's no specificity with a join. — Sandra Rossi, Nov 24 '22 at 15:01

Mathias Kemeter · Accepted Answer · 2022-11-24T16:03:05.340

4

You can create table variables either by defining the structure and inserting values or you can create it by direct assignment. When using the variable name in a statement, you need to prefix with a colon. Please find some examples in the code below:

DO BEGIN
    
    -- declaring table variables
    DECLARE tab1 TABLE(ID INTEGER, PHRASE VARCHAR(20));
    DECLARE tab2 TABLE(ID INTEGER, PHRASE VARCHAR(20));
    
    -- filling values
    INSERT INTO :tab1 VALUES (0, 'Hello');
    INSERT INTO :tab1 VALUES (1, 'Hallo');
    INSERT INTO :tab2 VALUES (0, 'World');
    INSERT INTO :tab2 VALUES (1, 'Welt');

    -- joining both
    SELECT a.PHRASE, b.PHRASE
    FROM :tab1 a
    JOIN :tab2 b ON a.ID = b.ID;

    -- declaring table variable via assignment
    tab3 = SELECT 0 ID, '!' PHRASE FROM DUMMY;

    -- joining all three
    SELECT a.PHRASE, b.PHRASE, c.PHRASE
    FROM :tab1 a, :tab2 b, :tab3 c
    WHERE a.ID = b.ID;
    
END;

More detailed information with examples can be found in the documentation:

edited Nov 24 '22 at 16:03

answered Nov 24 '22 at 07:58

Mathias Kemeter

933
2
11

2

Thank you!! I really appreciate it. The subtle syntax differences between HANA and SQL Server, along with HANA's poorer documentation and less users makes it a bit harder to get the syntax right, and sometimes just one working example is all you need to see. I appreciate it. – David Mays Nov 24 '22 at 10:15
2

I have added two documentation links with more details and a couple of examples. – Mathias Kemeter Nov 24 '22 at 16:03
Is it possible there are versions of HANA that do not allow this syntax? Mine will run this far with no problems: do begin declare tab1 table(inputs varchar); end But as soon as I try and interact with the table variable it says "incorrect syntax neat ":tab1"" do begin declare tab1 table(inputs varchar); insert into :tab1 values("Hello World!"); -- Add this and it breaks. end – David Mays Dec 05 '22 at 22:37
There shouldn't be a difference between the versions. However, there are two bugs in your script: Your VARCHAR field does not have a length and you use double quotes for Strings. Double quotes are interpreted as field names, which probably causes the incorrect syntax issue. Use this instead: `declare tab1 table(inputs varchar(100)); insert into :tab1 values('Hello World!');` – Mathias Kemeter Dec 06 '22 at 07:41
I must have not two but three bugs, sorry for the 2 red herrings (which I'm sure would have caused errors later) but this still returns sql syntax error near :tab1 [ do begin declare tab1 table(inputs varchar(20)); insert into :tab1 values('Hello World!'); end ] I also realize Stack Overflow is clearly not supposed to be everyone's personal linter, but I've struggled to get this to take for so long I've finally started asking questions haha. Our system is running on the HANA db, and not on Microsoft SQL Server like some, but the HANA docs do seem to indicate it's possible. – David Mays Dec 06 '22 at 15:08
I'll have to find out our exact SAP instance. Others have had issues earlier than SPS09, but I'd hoped that since it let me declare the variable, I'd for sure be able to access it: https://answers.sap.com/questions/11940310/can%27t-declare-table-variable.html – David Mays Dec 06 '22 at 15:23
Please be aware, that SPS09 in this case is referring to HANA1, which is out of maintenance for quite a while. Current on-premise release is HANA2 SPS06. To be honest, with my statement that this should run on any system, I was not envisioning HANA1 SPS08 or earlier. – Mathias Kemeter Dec 06 '22 at 20:04

How to create a table variable for using in a JOIN?

1 Answers1