Re: SQL Question

Hi Chuck,
Thanks for the tip.
With the help of others on the list, I found the missing parentheses and
the missing comma.

Your suggestion for using the Searched When for all of the CASE statements
is interesting and I will bear that in mind for future programs.

I appreciate you taking the time for a detailed analysis of the code and
your suggestions for improving it.

Thanks again,

On Tue, Apr 24, 2012 at 6:44 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 24 Apr 2012 13:35, Jeff Young wrote:

I have the following SQL Statement:
<<SNIP; replaced with my own reformatted:>>

Here I have reformatted what was given, without any _corrections_
made; i.e. merely adding white-space and CRLF. The missing
right-parentheses for the many SUM() should be conspicuous with that new
formatting. So too should the missing comma in the list of host variables.

<code>

Exec SQL
Select
-- Payment Transaction Count
Sum(Case EpArTrnTp
When 'PA' Then 1 Else 0 End)
-- Payment Total Amount
, Sum(Case EpArTrnTp
When 'PA' Then EpArTrAm Else 0 End
-- Invoice/Credit Transaction Count
, Sum(Case
When EpArTrnTp in ('IN','CM') Then 1 Else 0 End)
-- Invoice Total Amount
, Sum(Case EpArTrnTp
When 'IN' Then EpArTrAm Else 0 End
-- Credit Total Amount
, Sum(Case EpArTrnTp
When 'CR' Then EpArTrAm Else 0 End
-- Adjustment Transaction Count
, Sum(Case
When EpArTrnTp in ('DM','NC') Then 1 Else 0 End)
-- Adjustment Total Charge Back Amount
, Sum(Case EpArTrnTp
When 'DM' Then EpArTrAm Else 0 End
-- Adjustment Return Check Amount
, Sum(Case EpArTrnTp
When 'NC' Then EpArTrAm Else 0 End
Into
:Payment_Transaction_Count
, :Payment_Total
, :Off_Line_Transaction_Count
, :Off_Line_Invoice_Total
, :Off_Line_Credit_Total
, :Adjustment_Transaction_Count
, :Adjustment_CB_Total
:Adjustment_Debit_Total
From VXbEpDArt
Group by EpArTrnTp; // Invoice & Credits Only

</code>

My fields are defined as follows:

D Off_Line_Invoice_Total..
D S Like(##Bal)
D Off_Line_Credit_Total...
D S Like(##Bal)
D Off_Line_Transaction_Count...
D S Like(##Trn_Cnt)
D Adjustment_Debit_Total...
D S Like(##Bal)
D Adjustment_Credit_Total...
D S Like(##Bal)
D Adjustment_CB_Total...
D S Like(##Bal)
D Adjustment_Transaction_Count...
D S Like(##Trn_Cnt)
D Payment_Total...
D S Like(##Bal)
D Payment_Transaction_Count...
D S Like(##Trn_Cnt)

##Bal = 11.2
##Trn_Cnt = 3.0

When I attempt to compile, I receive the following messages from the
pre-compiler:
Position 18 Token EPARTRNTP was not valid. Valid tokens: , FROM INTO.
Position 12 Keyword INTO not expected. Valid tokens: ) ,.
Position 14 Indicator variable ADJUSTMENT_DEBIT_TOTAL not SMALLINT
type.

What am I doing wrong?

IMO the primary problem is formatting. I understand that is arguably
subjective, but there seems to be a very strong case for making all of
the commas align. As a prefix on each successive expression, aligned in
the same position from the beginning of the row, that makes the commas
or their absence much more conspicuous than when they are
trailing\ending the variable-length lines. The following rewritten
partial version, for example, to see the commas aligned versus imagine
them at the end of the lines and thus unaligned:

<code>

Exec SQL
Select
-- Payment Transaction Count
Sum(Case EpArTrnTp When 'PA' Then 1 Else 0 End)
-- Payment Total Amount
, Sum(Case EpArTrnTp When 'PA' Then EpArTrAm Else 0 End)
-- Invoice/Credit Transaction Count
, Sum(Case When EpArTrnTp in ('IN','CM') Then 1 Else 0 End)
-- Invoice Total Amount ...

</code>


Similarly for use of the CASE, because not all can be written using
the simple-When form [equality test implied], they might best all be
consistently written using the searched-When format to make everything
align even more. If all nearly-identical CASE can be grouped, then that
might help make errors even more conspicuous as well; e.g. current
count\total are interwoven, rather than all counts followed by all
totals. Another issue is that SUM() requires a NULL indicator, or to be
coalesced such as with IFNULL(SUM(...),0). Compare the following
rewritten version [with more formatting for alignment, and added
indicator variables] to the initially rewritten version, and notice how
easily the closing parenthesis for each SUM can be seen along with each
Then,Else,End of each CASE as well. Every CASE is also started the same
using the searched-When so all of the first three tokens within the
SUM() are also aligned; and while all of the "=" could be written as
"in" predicates to have even more consistency, that might be a bit too
aggressive ;-)

<code>

Exec SQL
Select
-- Payment Transaction Count
Sum(Case When EpArTrnTp = 'PA' Then 1 Else 0 End)
-- Payment Total Amount
, Sum(Case When EpArTrnTp = 'PA' Then EpArTrAm Else 0 End)
-- Invoice/Credit Transaction Count
, Sum(Case When EpArTrnTp in ('IN','CM') Then 1 Else 0 End)
-- Invoice Total Amount
, Sum(Case When EpArTrnTp = 'IN' Then EpArTrAm Else 0 End)
-- Credit Total Amount
, Sum(Case When EpArTrnTp = 'CR' Then EpArTrAm Else 0 End)
-- Adjustment Transaction Count
, Sum(Case When EpArTrnTp in ('DM','NC') Then 1 Else 0 End)
-- Adjustment Total Charge Back Amount
, Sum(Case When EpArTrnTp = 'DM' Then EpArTrAm Else 0 End)
-- Adjustment Return Check Amount
, Sum(Case When EpArTrnTp = 'NC' Then EpArTrAm Else 0 End)
Into
:Payment_Transaction_Count :iPTC
, :Payment_Total :iPTA
, :Off_Line_Transaction_Count :iOTC
, :Off_Line_Invoice_Total :iOIT
, :Off_Line_Credit_Total :iOCT
, :Adjustment_Transaction_Count :iATC
, :Adjustment_CB_Total :iACT
, :Adjustment_Debit_Total :iADT
From VXbEpDArt
Group by EpArTrnTp; // Invoice & Credits Only

</code>

Regards, Chuck
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.