I have a scenario in a new database that I am adding to my universe.
There are 3 tables - A/B/C
A and B are just link tables in a normalised database- C is the main table!
A joins to B and B joins to C to get a parent transaction
However…
A can also join to C in certain situations to get children transactions of the parent transaction!
eg person buys something - data is entered in A, B and C (transaction 1)
that person returns what they bought, so now transaction 1 needs to have a refund associated
This is done by updating a field for the row in C with transaction 1 and making a new transaction in C which only links back to transaction 1 via A joining to C.
So I need to represent this scenario in the database- I’ve worked out the sql to get all transactions for this customer :
select C. *
from C, B, A
where ((C.TRANSACTIONID = B.CHILD_TRANSACTION__ID
and B.PARENT_TRANSACTIONS_ID = A.HJID) or (C.CHILDTRANSACTIONS_TRANSACTIO_0 = A.hjid
and B.CHILD_TRANSACTION__ID = C.TRANSACTIONID
and C.CHILDTRANSACTIONS_TRANSACTIO_0 is null
))
The end result looks like this
Initial transaction 1 (purchase)
(see step 1 in attached file)
Next refund is processed - so C.childid gets updated with 4104 in the row for transaction 1
(see step 2 in attached file)
and the refund transaction is now entered into C as c.transid = 4101
and there are 2 rows in C now for the 1 customer.
(see step 3 in attached file)
I don’t know how to represent this in a universe though?!!
I need to be able to bring back both rows in a report!
Any help would be very kindly appreciated! ( I hope I’m making sense!) HOW TO JOIN.xls (18.0 KB)
How about a complete separation of refunds from purchases? To do so you will need to alias C table --> C_purchases will contain only purchases and C_refunds will have only refunds. Then both tables will be joined to all tables where it’s needed and there will be 2 context - 1 for purchases and 1 for refunds.
I’ve been working hard at this (and another problem) to get it working with SQL and tested- in the end I made a view doing a union of 1 query to show me all purchases and 1 query to show me all refunds- and then used this view in my main query
However I’ve ended up with an ‘or’ join in my SQL and I don’t know how to represent this in the universe!!!
the SQL is as follows
select distinct o.accountid, p.purchaseddealid, p.TRANSACTIONS_TRANSACTIONS_ID dealtransid, s.TRANSACTIONS_TRANSACTIONS_ID subid,
tsr.hjid,tsr.TRANSACTIONID,tsr.DATEELEMENT, tsr.dealid, tsr.status
from purch_purchaseddeal p,purch_purchaseddeal_link ppd,purch_order_purchaseddeal pd,
purch_order o, vwpurch_transaction tsr, purch_subscription_link sl, purch_purchaseddeal_subs st, purch_subscription s
where o.DEALS_PURCHASEDDEALS_ID = pd.HJID
and pd.hjid = ppd.PARENT_PURCHASEDDEALS_ID
and ppd.CHILD_PURCHASEDDEAL_ID = p.PURCHASEDDEALID
and p.SUBSCRIPTIONS_SUBSCRIPTIONS__0 = st.HJID(+)
and st.hjid = sl.PARENT_SUBSCRIPTIONS_ID(+)
and sl.CHILD_SUBSCRIPTION_ID = s.SUBSCRIPTIONID(+)
and (p.TRANSACTIONS_TRANSACTIONS_ID = tsr.HJID
or s.TRANSACTIONS_TRANSACTIONS_ID = tsr.hjid)
How do I represent this in the universe? (p.TRANSACTIONS_TRANSACTIONS_ID = tsr.HJID
or s.TRANSACTIONS_TRANSACTIONS_ID = tsr.hjid)
If I do a join with the 3 tables involved, it will come out as ‘AND’ not ‘OR’!!!
Any help is really appreciated as I’ve been banging my head against the wall over this and other problems for about 3 weeks and I feel like I’m so close…but not there yet!!!
Seconded. Self-restricting joins on the two tables - they serve two different purposes and you can report on the refund and purchase dates for the same transaction then by aliasing the date table accordingly.
I did try that way but I couldnt get the rows to display underneath each other for each customer - however I’ve never used contexts and so I was trying to do it via SQL
im sorry to ask but could you give me an idea if this is correct
enter alias table C- so now there is C_purch and C_refund
I don’t understand how I can join them though as where there is a refund - the only way to link it back to a customer is by looking at the purchase accountid
so for purchases I would join
C_purch .TRANSACTIONID = B.CHILD_TRANSACTION__ID
and B.PARENT_TRANSACTIONS_ID = A.HJID
and for refunds I would join
C_purch.CHILDTRANSACTIONS_TRANSACTIO_0 = A.hjid
and B.CHILD_TRANSACTION__ID = C_refund.TRANSACTIONID
and then create a context for each to show which is a purchase and which is a refund?
I’ve tried that in CR XI it wouldnt let me add in the transactionid from both c_purch and c_refund saying ‘Query generated multiple SQL statements, however multiple SQL is not supported in Crystal Reports Query Panel’
I don’t understand how contexts will work as I couldnt even make this work in SQL- there are other tables linking into A which give the accountid etc and they join to only the purchase transaction…
in my example attached they will only join to the transaction 2696- i can’t see how to make an accountid to join to 4101 also!
(BOB member since 2007-06-13)
(BOB member since 2003-11-27)
(BOB member since 2007-06-13)
(BOB member since 2007-06-13)