Re: [Emerald] Query / rebatch
Dale E. Reed Jr. ( (no email) )
Sat, 26 Jun 1999 21:31:02 -0700
Alexander Blauvelt wrote:
>
> Hi Dale and crew,
>
> I need a query that will pull the username (login + "@olg.com" type
> thing) and full name of everyone in the batch screen with failed
> transactions. I don't want the expired cards (E1), just ones that are
> ND and NR.
>
> The query I came up with is this:
>
> select sa.login + '@olg.com', ma.firstname + ' ' + ma.lastname
> from ExternalTrans e, MasterAccounts ma, SubAccounts sa, invoices i
> WHERE ma.customerid = sa.customerid
> AND e.InvoiceID = i.InvoiceID
> AND ma.CustomerId = i.CustomerID
> and (e.response = "ND" or e.response = "NR")
> and e.transtype != 8 and e.transtype != 7
> and sa.sendbill=1
>
> However, this pulls 518+- customers and the batch list only has 300
> failed transactions, including the expired cards. What other criteria
> am I missing? What criteria is used to display the transactions in the
> batch window ? I need that query with a small modification to remove
> expired cards from the list.
The problem is that you are pulling in the SubAccounts table, which
is bring in multiple rows for a single MBR. What you need to do is
make sure that the SendBill flag is set on one (and only one) SA for
each MBR. Otherwise, you have to play tricks to only get one row
per MBR. Emerald does the same query as you above, but consolidates
the list of services to make one email and a To: line with all the
addresses.
--
Dale E. Reed Jr. Emerald and RadiusNT__________________________________________IEA Software, Inc. www.iea-software.com