This content has been marked as final. Show 12 replies
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
Sorry, I was unclear.
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.
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.
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
Then I don't see what causes this problem.
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.