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
Advertisement
Answer
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.