Re: sum in SQL? -- MIDRANGE-L

Nah, too much effort, even if you're doing it for a report. What's even
cooler is the ability to prompt for the variable when the user clicks an
icon on their desktop, and launches the ViewPoint piece of the software.

I just did a project for a JDE client in Cleveland. The client was having
problems with highlighting its problem receivables. I created a view where
the collector is prompted for a collector code and the number of days old
for outstanding invoices. A few seconds after clicking OK, a display pops up
showing customer number/name, amount, invoice number, contact info and age
of the invoice. This is sorted in descending order by age, so the collector
can "go get 'em". Also, while the collector is talking to the customer, and
needs to see details about an invoice, they can then "drill down" to the
invoice line item level.

If the customer requests that the collector provide this backup information,
a couple of clicks sends a spreadsheet to the customer via email, or routes
it to a Quadrant Fast Fax output queue on the 400. BTW, this works in both
the green screen side and the ViewPoint side. Total amount of development
time on my part: 3.5 hours, much of which was spent figuring out some of
their UDC codes. I know how smart you are, Don, but I'm not sure even you
could do the same with embedded SQL in the same amount of time. :-))

The best part of this is that in the month since I built this stuff, the
client reports that their average number of receivable days has been reduced
from 53 to 48 (one month!). This has a huge impact for them, since the
receivables have a direct relationship to their line of credit with the
bank. In fact, the reason this came about was that the bank told them that
if any customer's invoice got aged past 60 days, that customer's ENTIRE
receivable balance would be disqualified from the borrowing base. Pretty
serious, huh?

The collectors love the tool much more than having to use a report, and the
collections manager just had me build a view that shows her all of the
customers without being prompted for a collector code.

Paul Nelson
630-327-8665 Cell
708-923-7354 Home
----- Original Message -----
From: "Don" <dr2@cssas400.com>
To: <midrange-l@midrange.com>
Sent: Wednesday, December 11, 2002 9:30 PM
Subject: Re: sum in SQL?


>
>
> Paul,
>
> if you're going that far, I would prefer to just use the embedded
> SQL...
>
>
>
> On Wed, 11 Dec 2002, Paul Nelson wrote:
>
> > You need to check out SEQUEL from Advanced Systems Concepts
> > www.asc-iseries.com
> > Passing runtime variables is a piece of cake. If you want to be creative
and
> > do some editing, you wrap a CL program and prompt screen around the
> > view/report. If you want some examples, let me know off list.
> >
> > Paul Nelson
> > Arbor Solutions, Inc.
> > 630-327-8665 Cell
> > 708-923-7354 Home
> > ----- Original Message -----
> > From: "James Rich" <james@eaerich.com>
> > To: <midrange-l@midrange.com>
> > Sent: Tuesday, December 10, 2002 6:49 PM
> > Subject: Re: sum in SQL?
> >
> >
> > > On Tue, 10 Dec 2002, Peter Dow wrote:
> > >
> > > >     SELECT CUSTNBR, CUSTNAME, SUM(INVTOT), CUSTYTD
> > > >       FROM CUSTOMERFILE JOIN INVOICEFILE ON CUSTNBR = INVCUSTNBR
> > > >      WHERE INVMONTH = 12
> > > >      GROUP BY CUSTNBR, CUSTNAME, CUSTYTD
> > > >
> > > > I'm assuming that CUSTYTD is a field in your CUSTOMERFILE.
> > >
> > > Excellent!  Good assumption, the YTD totals are in the customer file.
> > > Here is what I used:
> > >
> > > select cmcst#6, cmcst#3, cmcsname, sum(ihinvtot), cmytdsal
> > > from custmp, invchp where
> > > cmcst#6=ihcst#6 and cmcst#3=ihcst#3 and ihinvdate>'2002-11-30'
> > > group by cmcst#6, cmcst#3, cmcsname, cmytdsal
> > >
> > > Now I wonder how I can pass a date to this from say a CL program?  And
> > > make it print instead of display?  I want to put this on a menu that
> > > prints out the results for the current month.
> > >
> > > James Rich
> > >
> > > _______________________________________________
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > > To post a message email: MIDRANGE-L@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@midrange.com
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > >
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> > To post a message email: MIDRANGE-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > or email: MIDRANGE-L-request@midrange.com
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>