Hello All,
I've been wrestling with a subquery for the better part of a day now.
I have a user who would like an output set element for the paid amount of tickets donated for resale. It would be fairly straight forward except the dollar amount is not held in the donated row but the void row in T_SUB_LINEITEM. I created a new view that has a WHERE clause limiting the sli_status to just void and donate. But there are a lot of void that don't have a matching donate row.
Then in TR_ELEMENT_GROUP use a SELECT statement with a subquery to return only the void rows that have a ticket_no shared with a donate row. My last attempt for the Data From field was:
(SELECT a.* FROM LVS_SUB_LINEITEM a WHERE a.ticket_no = (SELECT b.ticket_no FROM LVS_SUB_LINEITEM b WHERE a.ticket_no = b.ticket_no))
Which unfortunately doesn't return any data when used in an output set. Anyone have any ideas about this? Or if there's another/better way to do this?
Any help would be greatly appreciated.
Thanks
I'd start by building a purpose-built view to (looks like you already have one?) so that all the work happens there and you don't have to worry about what works or doesn't work when entered into TR_ELEMENT_GROUP.
However, in the meantime, check out T_SLI_DETAIL, which has due, paid, and a benevolent return indicator:
detail_sli_no sli_no due_amt paid_amt pmap_no ben_ind240152 2346 135.00 135.00 21140 Y
Thanks Gawain. I'll take a look at T_SLI_DETAIL.