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
In case the solution is something simple, I did notice in your query that you should have an "IN" instead of an "=".
WHERE a.ticket_no = (SELECT... should be WHERE a.ticket_no IN (SELECT....
Thanks Neil. Changing out "=" for "IN" produces too many rows. More than 100 per constituent with Single Row checked and unchecked on the element. Wish it had been something simple.