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!

Answer 1

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:

• Table Variable Type Definition
• Table Variables