Okay, I have found a way to solve my problem but I need to know if there are any pitfalls with my solution. I have two tables that need to be left outer joined on 3 columns. In addition, I need to limit 4 columns on one of the tables but NOT include those in the join (in other words, prefilter the join). When I put all 7 criteria into the where clause and tell DI it is an outer join, it puts all the criteria into the join.
IE:
select *
from tbl_21, tbl_23
WHERE (TBL_23.I_CLIE = TBL_21.I_CLIE (+) )
AND (TBL_23.I_ALRT = TBL_21.I_ALRT (+) )
AND (TBL_23.T_ALRT = TBL_STAG_AM9721.T_ALRT (+) )
AND (TBL_21.I_TYPE_KEY (+) = 'LOCID')
AND (TBL_21.F_STATS_RECR (+) <> 'I')
AND TBL_21.T_MODF (+) >= to_date('01-JAN-2008', 'dd-mon-yyyy')
AND TBL_21.T_MODF (+) < to_date('19-MAY-2008', 'dd-mon-yyyy')
What I did was create a pushdown_sql with all the join criteria and pre-filter criteria manually specified and removed the outer join in DI. This generates:
select *
from tbl_21, tbl_23
WHERE TBL_21.I_TYPE_KEY = 'LOCID'
AND TBL_21.F_STATS_RECR <> 'I'
AND (TBL_21.T_MODF >= to_date('01-JAN-2008', 'dd-mon-yyyy')
AND TBL_21.T_MODF < to_date('19-MAY-2008', 'dd-mon-yyyy'))
AND TBL_23.I_CLIE = TBL_21.I_CLIE (+)
AND TBL_23.I_ALRT = TBL_21.I_ALRT (+)
AND TBL_23.T_ALRT = TBL_21.T_ALRT (+)
Which is what I want. My question is, what are the dangers here? I know about it being harder to maintain in a pushdown, but at least it is still in DI not an SP. Is there a better way?
G’day all, first time poster (so be gentle) but have been using BOB to get me over various hurdles met in my short time using DI. This is the first time I’ve hit a hurdle where the suggestion found in BOB is not getting me to my result.
I’ve noticed that two datastores outer-joined directly via a query are not being pushed down so I attempted to do the above but am not finding it is doing for me what the above poster found it did for him, any suggestion as to where I have gone wrong will be appreciated.
As stated above, without interference, DI is running to separate queries so initially I tried
But this resulted in the optimized SQL removing tbl2 from the table list (and subsequent errors that tbl2 fields don’t exist)
I then tried
tbl1.key = tbl2.key and pushdown_sql('DS', 'tbl2.ind (+) = \'Y\')
with an outer join specified (in the outer join tab) but validation failed because pushdown_sql and outer joins aren’t allowed together.
I was tempted to use a view to make the join but as we haven’t used views previously (in the project rather than ever) so looking to keep in DI if at all possible.
We need a bit more information. You mentioned two datastores… are they on the same database or is there a database link between the databases? Otherwise it is not physically possible for DI to pushdown the SQL.
I suspect that it is because they are in different datastores and pushdown is looking at only one datastore. I take it the two are separated for some essential business need?
(BOB member since 2005-09-16)
(BOB member since 2004-12-17)
(BOB member since 2004-12-17)