on 04-25-2013 1:23 PM
Hi All
I have a case where the Quotation can exist without orders and order can exist without quotation, while calculating some attributes I need to Full outer Join, How can we achieve FULL OUTER JOIN as full outer join is not upported in CE functions and also in Analytical Views.
Request Your Ideas.
Thanks
Santhosh Varada
Hi Santosh,
the classic approach here:
1. Outer-Join orders
2. Outer-Join quotes
3. UNION both sets
If you just use UNION ALL you need to make the data set distinct afterwards, e.g. by aggregating against a constant.
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars,
When i was replying to this, you too were replying for the same discussion. As i am slow in typing keyboard you posted ahead of me.
I am sorry if you feel we both given the same response.
By the way i never heard of orders and quotes. Its means the same which i replied or something different?
Regards
Raj
Lars Breddemann wrote:
Hi Santosh,
the classic approach here:
1. Outer-Join orders
2. Outer-Join quotes
3. UNION both sets
If you just use UNION ALL you need to make the data set distinct afterwards, e.g. by aggregating against a constant.
- Lars
Hi Lars and Raj
Thanks for the quick reply, but I want to perform UNION as UNION eliminates duplicate recorrd , is it only using SQL statement to perform the UNION because I did not find CE function for Union when I tried with CE_UNION , it failed.
Also can you elaborate on aggregating against a constant incase of UNION_ALL.
Thanks
Santosh
Hi Raj,
no need to feel sorry at all.
This is what these forums are made for: to get answers from a group of people, not just from that one guy you happen to know
And apparently we both had the same idea; so the only reason to be sorry would be if we both would be wrong...
So, don't worry - all good and well appreciated!
Orders and Quotes are - as I simply assumed - just two tables that contain information for, well quotes (as in "pricing information" or "offers" - the stuff you get from your vendor when you ask her "how much does it cost, when I take 20 of it?") and orders - that is what you send to your vendor, when you actually want to accept the offer.
Not all quotes lead to an offer.
And not for every single offer I need a quote - I just take the list price or my last offer price for it.
- Lars
Hm... the CE_UNION function simply doesn't exist - no wonder, it failed
Having thought about this a bit longer than the initial 2,5 seconds I figure that you don't need to do a manual grouping against a constant for uniqueness.
The default for calculation view is to deliver unique records anyhow, as there is an implicit grouping, when at least one key figure/measure is defined.
So, CE_UNION_ALL should actually do the trick.
- Lars
Hi Lars
If atleast one measure is Involved LEFT_OUTER_JOIN columns and RIGHT_OUTER_JOIN columns CE_UNION_ALL would eliminate duplicate records.Else it would result in duplicate records.
suppose measure "NET_VALUE" is present in LEFT and RIGHT join tables then it would eliminate duplicates.
Please coorect me if I am wrong.
Suppose if I don't have any measure in LEFT and RIGHT tables how do we aggregate it against a constant , if possible an exampl would be helpful.
Thanks
Santosh
Hi Santosh,
Without going deeper if make sense or not and if performance will be good or not. You can do this:
1. Outer-Join orders
2. Outer-Join quotes
Duplicated entries of 1 and 2 belongs to INNER JOIN (what exist on both sides), so:
2.a. Projection of 2 with filter expression isnull(ORDERKEY). This will keep only outer records
3. UNION both sets
Regards, Fernando Da Rós
Message was edited by: Fernando Ros
Hi Fernando
Sorry for the delayed response, The above point by Lars works fine as the UNION ALL did not result in duplicates because the key figures are involved in the output as I understood it.
I believe your solution is also aimed at avoiding duplicates , but with out using UNION ALL statement ,please correct me if I am wrong, in that case for UNION operation do I need to write an SQL statement.
Thanks
Santhosh Varada
Hi Santosh,
UNION ALL do not remove duplicates, what have this power on your scenario is the calculated view as Lars pointed out to you:
"The default for calculation view is to deliver unique records anyhow, as there is an implicit grouping, when at least one key figure/measure is defined."
About UNION to UNION ALL (on SQL as CE don't have UNION option) is that UNION provoke a row store transformation to DISTINCT the values removing the duplicates, and the UNION ALL clause just stack both results on column store. UNION ALL is faster but may produce duplicates as there's no distinct.
So it's working because you aren't summing up any measure, if you have it the result for values which exist on both projections both projections (LEFT OUTER & RIGHT OUTER) will be represented with double values.
Regards, Fernando Da Rós
Hi Santhosh,
We can achieve the full outer join by using
CE_UNION_ALL of CE_LEFT_OUTER_JOIN, CE_RIGHT_OUTER_JOIN.
Now its look simple to you
Regards
Raj
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 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.