cancel
Showing results for 
Search instead for 
Did you mean: 

How ti Implement Full Outer using CE functions in SQL Script .

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

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

rindia
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

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    

Former Member
0 Kudos

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

former_member182114
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

former_member182114
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Fernando

   Thanks for the detailed explanation, I have few more questions on this topic, I will open a new thread for them.

Thanks

Santosh

Answers (1)

Answers (1)

rindia
Active Contributor
0 Kudos

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