Indicator variable in SUM statement.

callkris_cit · New User Joined: 22 Apr 2006 Posts: 44

Hi Guys,

I have the below query. I believe the below part should not work properly.

Because i feel we should not use indicator varialble, while using aggregate functions. Please ensure my thought is correct or wrong.

EXEC SQL
SELECT SUM (PAY_AMOUNT)
INTO :PAY-AMT :I-1
FROM TMEMBTABLE
WHERE MEMB = :MEMBER-NUM
END-EXEC.

EVALUATE SQLCODE
WHEN +0
IF I-1 < +0
MOVE ZEROS TO PAY-AMT
END-IF
WHEN +100
MOVE ZEROS TO PAY-AMT
END-EVALUATE

Bye
Suresh

callkris_cit · New User Joined: 22 Apr 2006 Posts: 44

I UNDERSTOOD INDICATOR IS REQUIRED.

BYE

SURESH

vijikesavan · Posted: Wed Nov 22, 2006 2:43 am

Hi,
If you dont want to use the indicator, try this.

EXEC SQL
SELECT VALUE (SUM (PAY_AMOUNT),0)
INTO :PAY-AMT
FROM TMEMBTABLE
WHERE MEMB = :MEMBER-NUM
END-EXEC.

This query will make the PAY-AMT zero on null values.
Hope I clarfied you!
Thanks,
Viji