× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Not sure this can be done right. But I am looking for ideas.

On our utility bills, we used to have an RPG program build files that then
are pulled to the internet SQL Server database. I am trying to cut out some
middle-man processes to pull the data directly from the tables in the
software.

On the web server we have the customer table, bill history table, and a
bill summary table. On the IBM i side, we have several customer tables, a
bill history table, and a payment history table. As I am writing this, I am
starting to think we need to rethink what we show on the internet, but for
the fun of it, let's continue on with the question.

The customer table and bill history tables are easy to pull. The
summary.... not so much.

The program pulls all bill history records into the summary table, then it
tries to match payments and payment adjustments to the bill (by
customer/due date). The major reason I want to get rid of this program is
because (as it is now anyway) it completely rebuilds the table every night,
I want to only pull new/changed/deleted record on a nightly basis.

I have the following query:

SELECT CCUSTN, CDUEDT, CASE CTYPE WHEN 'A' THEN 'ADJ' WHEN 'B' THEN 'BILL'
WHEN 'P' THEN 'PADJ' WHEN 'R' THEN 'RFND' WHEN 'S' THEN 'ASMT' WHEN 'V'
THEN 'VOID' END AS CTYPE, (SELECT MAX(B.HDPAID) FROM QMFILES.INTER00002 B
WHERE B.HCUSTN = A.CCUSTN AND B.HDUEDT = A.CDUEDT) AS PAID_DATE, CFINAL,
CPREVS, (SELECT SUM(B.HPDAMT) FROM QMFILES.INTER00002 B WHERE B.HCUSTN =
A.CCUSTN AND B.HDUEDT = A.CDUEDT) AS PAY_AMOUNT, TOTAL_BILL FROM
QMFILES.INTER00001 A

When comparing this against the file that is built nightly right now (for
the old system), the totals don't match. The RPG logic looks like:


C**********************************************************************************************
C* S U B R O U T I N E: WRITEPAY - Add payment info for a bill
*

C**********************************************************************************************
CSR WRITEPAY BEGSR
C*
C* Find a bill with the due date
C SUMKEY KLIST
C KFLD SVCUSTN
C KFLD SVDUEDT
C*
C SUMKEY SETLL INCUSSUM1
C RRSUM1 TAG
C SUMKEY READE INCUSSUM1 81
C *IN81 CABEQ *ON NOBILL
C*
C STYPE CABNE 'BILL' RRSUM1
match pymt to BILL
C*
C MOVE SVDPAID SPAYDT
C MOVE SVPDAMT SAMTPD
C UPDATE INCUSSUM1
C GOTO ENDWPAY
C*
C* If no bills for this payment, see if there is an adjustment it can
be tied to
C NOBILL TAG
C*
C SUMKEY SETLL INCUSSUM1
C RRSUM2 TAG
C SUMKEY READE INCUSSUM1 81
C *IN81 CABEQ *ON ENDWPAY
C*
C STYPE CABNE 'ADJ ' RRSUM2
match pymt to ADJ.
C*
C MOVE SVDPAID SPAYDT
C MOVE SVPDAMT SAMTPD
C UPDATE INCUSSUM1
C GOTO ENDWPAY
C*
C ENDWPAY TAG
C MOVE *ZEROS SVPDAMT
C*
C ENDSR

Any ideas how to put this logic into a SQL view (or stored procedure)? At
this point, this'll be for purely interest. I think I am going to go about
processing this differently.

--
Mike Wills
http://mikewills.me

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.