Re: sql if syntax help

On 24 Aug 2012 14:54, tim.dclinc@xxxxxxxxx wrote:

I am trying the following statement, but it doesnt seem to like the
"if"

I basically want total deposit amount, total IP deposit amount, and
total CP deposit amount.

SELECT
CLIENT_NAME,CLIENT_NO,
Sum(DEPOSIT_CHECK_AMOUNT) AS RECEIPTS,
Sum(AGENCY_FEE+PROCESSING_FEE) AS FEES,
if code='IP' then sum(deposit_check_amount) as iptot end if,
if code='CP' then sum(deposit_check_amount) as cptot end if,
Sum(deposit_check_amount-agency_fee) AS NET
FROM cashfile
WHERE BATCH_DATE>='08/01/2012' AND
CORP=1234
GROUP BY client_name,CLIENT_NO

Not sure what that message has to do with RPG, but...

The IF statement is an SQL Control Statement, for procedural SQL, not as an expression in a SELECT statement.

IBM i 6.1 Information Center -> Database -> Reference -> SQL reference
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzsqlcontstmts.htm
" _SQL control statements_

Control statements are SQL statements that allow SQL to be used in a manner similar to writing a program in a structured programming language. SQL control statements provide the capability to control the logic flow, declare and set variables ..."

Instead, the CASE Expression can be used in a SELECT. Although as coded [shown as an IF], the implied\intended effect would require that the "CODE" column be grouped.

IBM i 6.1 Information Center -> Database -> Reference -> SQL reference -> Language elements -> Expressions
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcaseexp.htm
" _CASE expression_

CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. ..."

Perhaps one of the following; presumably the latter?:


SELECT
CLIENT_NAME
,CLIENT_NO
,code
,Sum(DEPOSIT_CHECK_AMOUNT) AS RECEIPTS
,Sum(AGENCY_FEE+PROCESSING_FEE) AS FEES
,case When code='IP'
then sum(deposit_check_amount)
end as iptot
,case When code='CP'
then sum(deposit_check_amount)
end as cptot
,Sum(deposit_check_amount-agency_fee) AS NET
FROM cashfile
WHERE BATCH_DATE>='08/01/2012'
AND CORP=1234
GROUP BY client_name,CLIENT_NO,code


SELECT
CLIENT_NAME
,CLIENT_NO
,Sum(DEPOSIT_CHECK_AMOUNT) AS RECEIPTS
,Sum(AGENCY_FEE+PROCESSING_FEE) AS FEES
,Sum(case When code='IP'
then deposit_check_amount
end) as iptot
,Sum(case When code='CP'
then deposit_check_amount
end) as cptot
,Sum(deposit_check_amount-agency_fee) AS NET
FROM cashfile
WHERE BATCH_DATE>='08/01/2012'
AND CORP=1234
GROUP BY client_name,CLIENT_NO