on 02-20-2014 9:09 PM
I am in need of help here, if somebody wouldn't mind taking the time. If I have a Hana SQL Script procedure that inserts into a table variable, for example:
myTableVariable = select * from <dbTable>;
...Can I insert into :myTableVariable later on in the SQL Script? For example:
insert into :myTableVariable (<field_list>) select * from <dbTable2>;
The end result would be records from <dbTable2> appended to records from <dbTable> inside :myTableVariable.
I know I can create a temp table to achieve this, but if I want my Hana SQL Script to be "read-only", then I can't create temp tables (at least, as far as I know.)
Thanks for any help you can provide!
Dustin
(Thanks for the reference Patrick)
Hi Dustin,
You are correct - can't do insert on table variables at all, and can't do insert to temp tables if you want to keep it read-only.
Can you provide more details on your core requirement? Should help in addressing alternatives.
For a workaround though, consider the following. In most programming langues (all?) you can assign a variable a value that results from some calculation against the same variable's prior value.
So, with some integer "c", this is, of course, valid: c = c + 1; // java and other C-like languages
(Of course, increment operator also works: c++;)
Think of this same approach to achieve what you need, but in database perspective.
Sample code:
a = SELECT 1 AS "MY_FIELD" FROM DUMMY;
-- append a record without INSERT
a = SELECT 2 AS "MY_FIELD" FROM DUMMY UNION ALL SELECT "MY_FIELD" FROM :a;
Hope that helps a bit.
Cheers
Jody
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I was thinking a similar thing as Jody. If the general requirement is that you need to insert contents from two tables into one target table just for intermediate processing, then it can be assumed that you can make the sources identical to the target.
This sounds like a perfect use case for a union, since you really are just trying to combine these together, no real need to "insert" so to speak.
temp_table = SELECT <COLUMNS> FROM <TABLE1>
UNION
SELECT <COLUMNS> FROM <TABLE2>
Or this can likely be achieved in another way using multiple variables,
a = SELECT <COLUMNS> FROM <TABLE1>;
b = SELECT <COLUMNS> FROM <TABLE2>;
c = SELECT * FROM :a UNION SELECT * from :b;
Have not tested the above syntax as I'm not in front of a system, but seems logical.
Regards,
Justin
Hey Justin it was a very unclear reply from me .. apologies.. !!!
It depends on the requirement ( Yes for me too ) . As i was seeing the question where he got separate insert statements depending on different filter conditions, i assumed that it ensures that he doesn't have duplicate records coming up so proposed UNION ALL
Regards,
Krishna Tangudu
This helps a lot guys! Thank you for the prompt response.
Jody, it make sense for me to use the concept of c = c + 1. I applied this to my code to use CE_UNION_ALL to union 3 tables:
--tables AriesLoad1, AriesLoad2 and Reserves have table structures and are already filled with data.
FinalResult = ce_union_all(:AriesLoad1, :AriesLoad2);
FinalResult = ce_union_all(:FinalResult, :Reserves);
This seems to work good. One thing I am wondering, though, is how to "merge" the data instead of doing a union. The affect I'm looking for would be like doing an INNER JOIN on the tables while summing the key figures, and also including records in the result table that fall out due to the INNER JOIN. Almost like in ABAP, using a COLLECT statement instead of an APPEND statement. I don't know if there is a quick and clean way to do that in SQL Script.
I may just have to do an aggregation query after my union is complete:
FinalResult2 = select <fields>, sum(<key_figures>) from :FinalResult group by <fields>.
Does that make sense?
Dustin
Makes sense to me, you want to aggregate based on one or more of the columns of the FinalResult2 Union result. I think you'll have to do this in two steps as you laid out. That being said, it might not actually create a plan that does it like this, there may be optimizations along the way.
If you are using CE Script you will likely want to use a CE_AGGREGATION on table FinalResult2 so you are not mixing SQLScript and CE.
To be honest, I would be interested to see comparisons of doing this in SQLScript vs. CE from a performance perspective. In SP06, I have done similar comparisons and actually found the SQLScript version to be faster.
Regards,
Justin
I don't have enough data in my sandbox tables to notice the difference between SQL Script and CE functions. As we roll this out more, I'm sure I'll need to benchmark, as you are doing.
Thank you for the help with CE_AGGREGATION, hadn't seen that yet in the Help menu.
I appreciate your response!
Dustin
I wanted to quantify this difference, so I took a stab at it. Used a large dataset, and just performed a couple selects using fiscal year as the filters on each. I ran the same set of SELECTs, just changing the UNION to UNION ALL for the second iteration.
Conclusion is that UNION ALL is faster, but almost a negligible amount in this exercise. Likely to increase with result set size, but typically we'll be aggregating somewhat before the union even occurs.
SELECT COUNT(*)
FROM "SCHEMA_NAME"."CE12000"
WHERE "GJAHR" IN ('2013','2014')
RESULT -> 1,347,849,619
SELECT "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART", "WWMDC",SUM ("VVREV"),SUM("VVBBC")
FROM "SCHEMA_NAME"."CE12000"
WHERE "GJAHR" = '2013'
GROUP BY "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART","WWMDC"
UNION / (UNION ALL in second test)
SELECT "GJAHR", "PERIO", "BUKRS", "SPART", "VKORG", "FKART", "WWMDC",SUM ("VVREV"),SUM("VVBBC")
FROM "SCHEMA_NAME"."CE12000"
WHERE "GJAHR" = '2014'
GROUP BY "GJAHR", "PERIO", "BUKRS", "SPART","VKORG","FKART","WWMDC";
UNION DB average = 2.29s
UNION ALL DB average = 2.28s
The main difference from the VizPlan is the aggregation/de-duplication that takes place after the UNION. I was curious how this worked, so here are the plans.
UNION - notice the analytical search present after the union
UNION ALL - notice no analytical search, so it saves you the .01s
Regards,
Justin
Good point - I was thinking the same. So I took it one step further and made the individual queries more granular and removed the WHERE clause columns from the result. This should make the analytic search de-duplication work harder in theory because the WHERE clause criteria is no longer in the result set, there is a lot of duplicates and we are also increasing the size.
SELECT DISTINCT "BUKRS", "SPART", "VKORG", "FKART", "WWMDC","PAPH1", "PAPH2"
FROM "SCHEMA_NAME"."CE12000"
WHERE "GJAHR" = '2013'
UNION (UNION ALL in second case)
SELECT DISTINCT "BUKRS", "SPART", "VKORG", "FKART", "WWMDC","PAPH1", "PAPH2"
FROM "SCHEMA_NAME"."CE12000"
WHERE "GJAHR" = '2014'
UNION VizPlan - Average runtime is 4.13s
UNION ALL VizPlan - average is 4.27s when I remove the first execution - which I saw as an outlier at 10.6s.
So in this case, UNION is still faster, by about 4% - which is interesting.
Regards,
Justin
In the meantime (HANA 2.0 and HANA Cloud) things have changed: https://help.sap.com/docs/HANA_CLOUD_DATABASE/d1cb63c8dd8e4c35a0f18aef632687f0/36f718be73e745ceae053...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Dustin,
It's interesting you ask this question because this is essentially the reason I am using a write procedure because I have the need to do this same sort of thing. In my case I had to write to a temp table to keep track. In my thread you will see a comment from JODY where he says he mimics this behavior like you are asking. Maybe he will chime in here with more details.
-Patrick
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.