Outer Join failing where the join field is null??

[CanNeverThinkOfaName]

Hi

I have this following SQL statement where tables can join in various ways but I cannot get the data to return when I add in the last join

The sql is as follows

SELECT PURCH_ORDER.ACCOUNTID, PURCH_ORDER.ORDERDATEELEMENT, PURCH_ORDER.TOTALRATEDAMOUNT,PURCH_SUBSCRIPTION.FREQUENCY, 
PURCH_SUBSCRIPTION.FREQUENCYMULTIPLE, 
PURCH_TRANSACTION.DEALID, PURCH_TRANSACTION.STATUS, PURCH_TRANSACTION.TRANSACTIONSUBTYPE,
PURCH_SUBSCRIPTION.TRANSACTIONS_TRANSACTIONS_ID, PURCH_PURCHASEDDEAL.TRANSACTIONS_TRANSACTIONS_ID, PURCH_SUBSCRIPTION_TRANS.HJID
FROM PURCH_PURCHASEDDEAL_LINK, PURCH_PURCHASEDDEAL, PURCH_ORDER, PURCH_ORDER_PURCHASEDDEAL, PURCH_PURCHASEDDEAL_SUBS, PURCH_SUBSCRIPTION,
PURCH_SUBSCRIPTION_LINK,PURCH_SUBSCRIPTION_TRANS, PURCH_TRANSACTION_LINK, PURCH_TRANSACTION
WHERE   PURCH_ORDER.DEALS_PURCHASEDDEALS_ID=PURCH_ORDER_PURCHASEDDEAL.HJID 
AND  PURCH_ORDER_PURCHASEDDEAL.HJID=PURCH_PURCHASEDDEAL_LINK.PARENT_PURCHASEDDEALS_ID 
AND PURCH_PURCHASEDDEAL_LINK.CHILD_PURCHASEDDEAL_ID=PURCH_PURCHASEDDEAL.PURCHASEDDEALID  
AND PURCH_PURCHASEDDEAL.TRANSACTIONS_TRANSACTIONS_ID = PURCH_SUBSCRIPTION_TRANS.HJID 
AND  PURCH_SUBSCRIPTION_TRANS.HJID=PURCH_TRANSACTION_LINK.PARENT_TRANSACTIONS_ID  
AND  PURCH_TRANSACTION_LINK.CHILD_TRANSACTION__ID=PURCH_TRANSACTION.TRANSACTIONID 
AND  PURCH_PURCHASEDDEAL.SUBSCRIPTIONS_SUBSCRIPTIONS__0 =PURCH_PURCHASEDDEAL_SUBS.HJID 
AND PURCH_PURCHASEDDEAL_SUBS.HJID = PURCH_SUBSCRIPTION_LINK.PARENT_SUBSCRIPTIONS_ID
AND PURCH_SUBSCRIPTION_LINK.CHILD_SUBSCRIPTION_ID = PURCH_SUBSCRIPTION.SUBSCRIPTIONID 
--and PURCH_SUBSCRIPTION.TRANSACTIONS_TRANSACTIONS_ID = PURCH_SUBSCRIPTION_TRANS.HJID(+)
AND TRUNC(PURCH_ORDER.ORDERDATEELEMENT) = TRUNC(SYSDATE) -1

When I run this I get 9 rows returned.
However when I try to add this join in
(commented out above)

PURCH_SUBSCRIPTION.TRANSACTIONS_TRANSACTIONS_ID = PURCH_SUBSCRIPTION_TRANS.HJID(+)

no rows return at all
I've tried both left and right outer joins and neither works.

The 'PURCH_SUBSCRIPTION.TRANSACTIONS_TRANSACTIONS_ID' field is null when I get the 9 rows returning data - and can't join to 'PURCH_SUBSCRIPTION_TRANS.HJID' while it is null- however there are times when it will be null but othertimes when it is filled in!

Is there anyway to represent this scenario please?!!!
I want to add in this join but still get my 9 rows to return

I hope I make sense!!!

Thanks so much for any help in advance!
I'm trying to get to grips with all the joins in a new database and then build a Business Objects Universe when I understand it all!

 

[LKBrwnDBA]

Try:

PURCH_SUBSCRIPTION.TRANSACTIONS_TRANSACTIONS_ID = NVL(PURCH_SUBSCRIPTION_TRANS.HJID(+),'?')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

You have more than one reference to HJID that needs fixed.

SELECT po.accountid,
       po.orderdateelement,
       po.totalratedamount,
       ps.frequency,
       ps.frequencymultiple,
       pt.dealid,
       pt.status,
       pt.transactionsubtype,
       ps.transactions_transactions_id,
       ppd.transactions_transactions_id,
       pst.hjid
FROM   purch_purchaseddeal_link     ppdl,
       purch_purchaseddeal          ppd,
       purch_order                  po,
       purch_order_purchaseddeal    popd,
       purch_purchaseddeal_subs     ppds,
       purch_subscription           ps,
       purch_subscription_link      psl,
       purch_subscription_trans     pst,
       purch_transaction_link       ptl,
       purch_transaction            pt
WHERE  po.deals_purchaseddeals_id           = popd.hjid
AND    popd.hjid                            = ppdl.parent_purchaseddeals_id
AND    ppdl.child_purchaseddeal_id          = ppd.purchaseddealid
AND    ppd.transactions_transactions_id     = [COLOR=red]pst.hjid[/color]
AND    [COLOR=red]pst.hjid[/color]                             = ptl.parent_transactions_id
AND    ptl.child_transaction__id            = pt.transactionid
AND    ppd.subscriptions_subscriptions__0   = ppds.hjid
AND    ppds.hjid                            = psl.parent_subscriptions_id
AND    psl.child_subscription_id            = ps.subscriptionid
AND    ps.transactions_transactions_id      = pst.hjid(+)
AND    trunc(po.orderdateelement)           = trunc(SYSDATE) - 1

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[CanNeverThinkOfaName]

LKBrwnDBA - that didnt work

BJCooperIT - thanks for taking the time to reformat my query in a more user friendly way!
However as you have written it, they are the correct links and pst.hjid does link to both ppd.transactions_transactions_id and ptl.parent_transactions_id - and should also link to ps.transactions_transactions_id but this is my problem