This content has been marked as final. Show 12 replies
Hi Greg,1 person found this helpful
What you can do is either :
- Create a yellow interface with your table Sales as source. Map the name column directly in the target. In the flow tab, click on your target and select "Distinct rows".
- Create a second interface, with your first interface as source. Select the "Use Temporary Interface as Derived Table (Sub-Select)" checkbox.
- Add your second datastore and join it. Or you can use a lookup table.
- Create an interface with Sales and P&R as source (or set P&R as a lookup table).
- Go on the flow tab and select "Distinct rows".
If you've a lot a data in the first table, I would go for the first solution.
Hope it helps.
Edited by: JeromeFr on Feb 14, 2013 9:52 AM
To be more clear in the first step of solution 1
Thank for quick answer. I have one question. I think that if I select distinct rows would it not select all rows that are distinct (P1, 30, 300| P1, 40, 100 - are different rows but product is tha same) when I need only product names? Correct me if I'm wrong.
Sorry, I was unclear.1 person found this helpful
In the first (yellow) interface, you should only have the Name column in the target. So if you check "Distinct rows", you will only have the distincts product names.
To explain a bit more :
Creating an yellow interface and using it as source with the "Use Temporary Interface as Derived Table (Sub-Select)" option will just use it as a subquery. You can check the generated code of your second interface to understand it.
The first solution explained in my first post will thus use a subquery like "SELECT DISTINCT name FROM Sales".
The second solution will join retrieve Alias for each row of the Sales table and then select the distinct pairs of name-alias. So if you've a lot of rows in Sales, I would avoid this one.
I have one more problem with yellow interface.
In target datastore (PROD_DISTINCT) i created one VARCHAR column. In target options I select:
Temporary Datastore Location to Work Schema.
However ODI still tries to create temporary datastore in my source where I do not have crete view permission.
Should I change this somewhere else?
You actually don't need to run the first interface. If you try to run it, it will try to materialize PROD_DISTINCT in the target schema.1 person found this helpful
If you just run the second interface AND you've selected the "Use Temporary Interface as Derived Table (Sub-Select)" option, it will just include the SQL code from the first interface as a subquery, and thus only the second interface will materialize a table.
I suggest you tu build the second interface as well and Execute it with the "Simulate" checkbox selected, you will see how it works.
Ok! One more question, is it possible to create yellow interface, make it as a source in another yellow interface, and use the second one in normal interface?
Best regards and big thanks for your help!
Yes it is.
And if you use twice the Derived Table option, you only need to execute the last interface.
Best regards and you're welcome !
Sorry for being such a noob but this is first time when I use yellow interface.
Use Temporary Interface as Derived Table (Sub-Select)" checkbox. is not active. I read that I can use that when:
"source datastore of your yellow interface is on the same database than the staging area of your blue interface."
My first yellow interface has: sorcue: PROD; stage:ODI_STAGE.
My second (which uses first as source): source: PROD, ODI_STAGE; stage:ODI_STAGE
My blue (which uses second yellow as source) source: Source: ODI_STAGE, Hyperion Planning (one more join); stage: ODI_STAGE.
Is it possible to do what we just talked about?
Don't worry, it's not a very intuitive option.
I think it should work with what you explained.
Could you check two things ?
- In the topology, under Physical Architecture, double click on Oracle technology. If "Support Derived Table" box is unchecked, check it. (found in the last two comments here : http://askankit.blogspot.be/2011/04/when-temporary-is-not-temporary.html?showComment=1308729768829#c4073543968078580502)
- in the overview tab of each interface, check that you select Oracle: ODI_STAGE
Unfortunatly both of those thigs are checked and I still cannot use it :/
Then I don't see what causes this problem.1 person found this helpful
You dragged and dropped the yellow interface in the source canvas of the following interface, right ?
Are you working with journalized table in your first interface? Or do you use a native sequence in one of the two yellow interface?
A limitation I forgot to mention is that you can't use journalized datastore and native sequence with derived tables.
Maybe just try to close ODI and start it again.
As a temporary workaround, you can create a database view for the distinct and use it as source.