Dear Community,
I'm wanting to return data from LT_TKT_HIST where payment method = x ....but pmt_method is not available in the table.
So I try to link LT_TKT_HIST to a table that has payment method... like T_PAYMENT by linking customer_no..but this does not seem to work and is returning everything regardless of payment type.
Can this be done? Help needed
---SQL Example:
SELECT DISTINCT
a.customer_no,a.perf_name,a.perf_dt,a.order_dt,a.tck_amt,a.num_seats,a.location,b.pmt_method
FROM LT_TKT_HIST a, T_PAYMENT b
WHERE
a.customer_no = b.customer_no ANDb.pmt_method = x
Hi Aaron
That's somewhere between impossible and just impossibly difficult.
There's no explicit link in Tess between a specific ticket purchase and the payment method that paid for it, mostly because of the necessity to deal with the complex possibilities of multiple/partial payments, exchanges, and so on. The links are complex, and involve moving through tables composed of payment fragments, transaction fragments, and the item fragments in the T_SLI_DETAIL table, and trying to re-assemble them into wholes. And then you need to consider that if a ticket is exchanged, it loses any connection to the original payment method anyway. That just shifts the level of difficulty up another notch. And unless you've done some customisation of LT_TKT_HIST, there's nothing in there to do the join on.
Kevin's suggestion will get you slightly closer, but would only work if every customer had only ever made one purchase. If you're happy with an approximate result, you could possibly link on the order number, and derive the most likely payment method via the order, assuming there's only one payment on the order and selecting the first one that pops up, but it needs reasonably good SQL skills to attempt something like that. .
I wouldn't even try, unless you were desperate - and if you are desperate, I'd recommend talking to Tess and getting them to provide someone like Peter Nelson to try and help. He still carries scars from trying to do this sort of thing at the Opera House.
Ken
Hey Ken,
My SQL skills have been dormant for quite some time so I best leave this one for the experts and see where it gets me. I wish it were as simple as including a payment method column in the table?
This little piece of work is only for a small group of constituents anyway.... probably no need for major changes at this point.
Thanks again,