[SOLVED] How to create a table variable for using in a JOIN?

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

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!

Solution

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: