This content has been marked as final. Show 10 replies
It's not possible within 10g.
There is two solutions to achieve your requirements :
- Duplicate the interface 6 times and load only one table in each. You should only insert new rows (not existing ones) if you want Union and not Union all.
- You can define a view making the union in your database.
I would definitely go for the second one. With the first one, you have code duplication which is not good. You'll have to maintain 6 interfaces.
Hope it helps.
You can try this :
Create an interface and drag only the datastore that are not used in these 3 subselect.
Then drag a column from ra to the canvas to create a filter. Edit it so it looks like this :
You only have to change ra, p and a by the aliases of your datastores.
(ra.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_asset) or p.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_prod_int) or a.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_asset))
Your generated SQL code should be similar to your original SQL script.
Thx a lot Jerome .it works
I have one more question:
This select captured changed datas.I will delete dataas which returns from this select.And then I will insert new datas.
I added to IKM COntrol append a new step like this:
delete from <%=odiRef.getTable("L","TARG_NAME","A")%> T WHERE (<%=odiRef.getTargetColList("", "t.[COL_NAME]", ", ", "\n", "UK")%>) in
I selected "UK" from target.
But I dont know that it works true.I selected PK_ID from source but which PK_ID Will I select?When targets UK is equal to source UK(PK_ID) then these datas will be deleted.Changed datas will be inserted.How can I modify this step?İs there any solution to do this step?Can I get dynamicly PK_ID?
Could you help me ?
I already had this problem.
ODI is adding a ( + ) on all your filters. I found 2 workarounds, but there is maybe a better solution :
1. Make the outer join by yourself by adding the ( + ) in your join condition (and thus by NOT checking the outer join checkbox). Drawback : You see it as an inner join on your canvas.
2. Use ordered-join. Check the box at the bottom of your join definition and give it a number. It will use the new ANSI syntax.
I resolved this problem like this:
I created new interface same as source not used subselect
I added to target columnt which is used in subselect.For example I added to target s_org_ext of row_id like so_row_id
row_id of s_asset-->sa_row_id
row_id of s_asset_xa-->xa_row_id
row_id of s_asset_xa2-->xa2_row_id
I created Second interface.And I used this filter there.İt worked
Thank your helping