Create temp table in HANA and put data using WITH clause

Question

How to put the result of this to temp table in HANA? I'm new to HANA. Right now it return correct data, but I need to put that or insert that on a table to connect it to other tables.

WITH monthss("MonthNumber") AS (
                SELECT 0 FROM "DUMMY" 
                UNION ALL 
                SELECT 1 FROM "DUMMY"
                UNION ALL 
                SELECT 2 FROM "DUMMY" 
                UNION ALL 
                SELECT 3 FROM "DUMMY" 
                UNION ALL 
                SELECT 4 FROM "DUMMY" 
                UNION ALL 
                SELECT 5 FROM "DUMMY"
                UNION ALL 
                SELECT 6 FROM "DUMMY"           
                UNION ALL 
                SELECT 7 FROM "DUMMY"
                UNION ALL 
                SELECT 8 FROM "DUMMY"                       
                UNION ALL 
                SELECT 9 FROM "DUMMY"
                UNION ALL 
                SELECT 10 FROM "DUMMY"                      
                UNION ALL 
                SELECT 11 FROM "DUMMY"
                UNION ALL 
                SELECT 12 FROM "DUMMY"                      
                UNION ALL 
                SELECT 13 FROM "DUMMY"          
            )  
             SELECT 
                ADD_MONTHS(current_date, -"MonthNumber") AS "Date"
             FROM monthss

Don't know HANA. But I remember noticing some old post that WITH is only possible in a SELECT. Dunno if they made it possible in recent versions. But then again, if you only use the WITH query once, you could use a sub-query instead. Personally I would create a permanent Tally table with just numbers, and select from that. — LukStorms, Feb 06 '20 at 14:54
Does this answer your question? [Does SQLScript for SAP HANA support the use of INSERT with CTEs (Common Table Expressions)?](https://stackoverflow.com/questions/48748810/does-sqlscript-for-sap-hana-support-the-use-of-insert-with-ctes-common-table-ex) — Sandra Rossi, Feb 06 '20 at 18:07

score 1 · Accepted Answer · answered Feb 06 '20 at 23:00

The purpose of the OP's query seems to be to create an auxiliary table (a helper table) that contains the dates of today and thirteen previous months.

The output is something like this (if current_date returns 2020-02-07):

|Date      |
|----------|
|2020-02-07|
|2020-01-07|
|2019-12-07|
|2019-11-07|
|2019-10-07|
|2019-09-07|
|2019-08-07|
|2019-07-07|
|2019-06-07|
|2019-05-07|
|2019-04-07|
|2019-03-07|
|2019-02-07|
|2019-01-07|

While using the UNION ALL-technique certainly works, it is tedious to write and maintain, only really works for small result set and is very verbose. Worse - IMO - it focusses on the "how" of constructing the result set, not on the "what" and "why".

Fortunately, there are alternatives to this.

Option 1 - GENERATE_SERIES

Use the GENERATE_SERIES function that has been available for years (it's even available in HANA 1 SP12):

SELECT
    GENERATED_PERIOD_START AS "Date"
FROM 
    SERIES_GENERATE_DATE ('INTERVAL 1 MONTH'              -- step length
                        , add_months(current_date, -13)   -- lower end
                        , current_date)                   -- upper END
ORDER BY 
    "Date" ASC;

This option is much more concise, explicit about the purpose (to generate a series of dates) and the specifics (step length, what's the min and max dates and what's the output order). Also, this is a lot faster (ok, this only comes to play, when creating larger ranges).

Option 2 - M_TIME_DIMENSION

Use the very HANA specific built-in calendar aux-table _SYS_REPO.M_TIME_DIMENSION

select 
    date_sql as "Date"
from
    _SYS_BI.M_TIME_DIMENSION td
where
    date_sql between add_months (current_date, -13)   
                 and current_date
    and day = extract (DAY from current_date) 
order by "Date" ASC;

While this is less readable, in my eyes, it is still better and more flexible than the original approach.
Also note, that this requires that the table has been filled (via "generate time data") to work.

Now, both of these approaches just produce the result sets, but the OP asked for how to store those into temporary tables. The obvious first answer is that both of the statements can be combined with INSERT. However, I would recommend not do that at all.

With option 2) the data is already in a table. If the query to produce the range should be reused across several independent statements then creating a SQL view would help (and be the right thing to do).

In case those independent statements, that would otherwise use the temp table, happen to be part of an SQLScript block, then the much better option is to simply assign the query result to a table variable and reference that table variable throughout the SQLScript block.

Create temp table in HANA and put data using WITH clause

1 Answers1

Option 1 - GENERATE_SERIES

Option 2 - M_TIME_DIMENSION