Full outer join with IDT

[Former Member]

Hello,

I would like to know how to make a full outer join at universe level with Information Design Tool, I read how to do it with Universe Designer (activating ANSI92 property) but I'm unable to do it with IDT.

IDT help said  "To create a full outer join, select the Outer join option below both tables. This join returns all rows from both tables, with null values when there is no match." but it's imposible to check both boxes at the same time.

Thank you !

[pavanbayyapureddi · Accepted solution]

Hi Alvez,

              Not sure you are still searching for answers close to two years! Thought this may help someone that may be searching now like me. I was also working on this and had to solve the puzzle! I found your posting and could not find the answer. But, this is the answer that worked for me. You will never be able to click 'Outer Join' on both the tables. That is not how it works.

Also the following resource was helpful.

http://scn.sap.com/docs/DOC-22058

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/f0cf2796-3d39-2e10-ffb2-80dfcca426e7&override...

[Former Member · Accepted solution]

Hi Pavan,

Yes, I could finally found the solution and of course you can check both side of the table, just enabling ANSI92 parameter in data properties.

Thanks for you response.

Regards,

Deiby Alvez

[Former Member · Accepted solution]

Hi Deiby,

Could you please tel me how to enable ANSI92 parameter in IDT for a Universe. I am using Teradata as the db for universe.

Thanks,

Karthik K

[Former Member · Accepted solution]

Hi Karthik,

Sure, it's on the Query Script Parameters, double click on your data foundation file in your project folder -> click on Properties and  under SQL Options, click on Parameters...

http://www.dallasmarks.org/blog/wp-content/uploads/2008/01/Information-Design-Tool-Query-Script-Para...

Regards,

[Former Member · Accepted solution]

Thanks Deiby.

Also can you tell if it is mandatory to created a Federation layer in the Data Foundation for multisource universe ?

Regards,

Karthik

[Former Member · Accepted solution]

Karthik,

I would recommend you to put this question in another thread, anyway take a look in the following documentation about multisource universe, I think that it´s pretty clear.

Regards,

[Former Member · Accepted solution]

I'm using Info Design Tool Version: 4.1 Support Pack 8 and I can say that I cannot find where to put the ANSI92 parameter in the Data Foundation.

Could anyone explain it?

[amood]

First you must be in the data foundation of your universe. Click on the Data Foundation bar:

Next click on the name of your universe which should be at the top of the panel where the Data Foundation bar is located:

When you click on that, you should see a similar window as below. Look in the lower right window for the "Parameters" button and click it.

That will open up the Edit Query Script Parameters window.

If ANSI92 is already showing and the value to the right says "No", click on the word "No". That will provide a drop down arrow that you will use to select "Yes". Then click OK.

***Now you must save your data foundation.

You should now be able to edit your joins and be able to check both boxes for Outer Join under each table:

Remember to save your data foundation after this step as well.

Best wishes for your success!

AM

[nscheaffer]

Very nice.

Thank you

[Former Member]

Hi Alvez,

I'm using Redshift Database and the univers designer (4.1 with a univers from 3.1)

I enable to Ansci92,  but still not able to check.

Can you help me?

Thank you

[pavanbayyapureddi]

Hi Mark,

             Try to follow my post with screen shots on 'Aug 13, 2014 10:59 PM'. It worked for me.

Best regards,

Pavan Bayyapureddi.

[pavanbayyapureddi]

You will never be able to click 'Outer Join' on both the tables. That is not how it works.

Also the following resource was helpful.

http://scn.sap.com/docs/DOC-22058

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/f0cf2796-3d39-2e10-ffb2-80dfcca426e7&override...

[Former Member]

Yes, you can check "Outer Join" on both tables, but it depends on the underlying DBMS.

Regards

[Former Member]

Hi Alvez,

I am having exactly the same issue while trying to create a full outer join between the 2 tables. I enables the ANSI 92 (set to Yes) in the paramaters dialog box but still not able to check both the boxes in IDT for the tables.

Can you help me in details on how I can achieve this?

[Former Member]

Hi Nimish,

What is your database ?

Regards,

[Former Member]

Hi Alvez,

I am using Microsoft SQL Server 2008 R2. Actually, I was able to enable the full outer join by adding ANSI 92 = Yes in IDT for both the business and data foundation layers.

Thanks for replying.