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 thread ...

Follow-Ups:

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

This mailing list archive is Copyright 1997-2020 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].