COUNT and GROUP BY Stmt when SELECT returns zero.

UDA
Enthusiast

COUNT and GROUP BY Stmt when SELECT returns zero.

SELECT count(distinct a.claim_ref_id)
INTO :v_RepClaimsTW
FROM fat_bse_cl_pay_cre_trans a
JOIN prt_lu_product b
ON (a.product_id = b.product_id)
WHERE (a.Reported_date_id between '2007/12/02' AND '2007/12/08'
AND b.Pr_Group_id not in ('1')) -- if executed down to here 0 is the count
GROUP BY a.claim_ref_id
HAVING (CAST(sum((a.F_cl_total_reserve * a.f_exch_rate)) AS NUMERIC(18,2))) <> 0.00; -- if executed down to here the count is blank.

If records exist then you get a valid count, if there are no claims to group on the count is blank. I understand why but my code says if SQLCODE <> 0 THEN....leave procedure.

Why does SQLCODE become 1 if the code executed okay?

or can this be wrapped up somehow to return 0(zero)?
2 REPLIES
Teradata Employee

Re: COUNT and GROUP BY Stmt when SELECT returns zero.

SQLCODE as 1 is the indicator for a "warning but successful termination"...

HTH!

Regards,

Adeel
SN
Enthusiast

Re: COUNT and GROUP BY Stmt when SELECT returns zero.

hi,

as it returns a blank...it means it didnt find any data(record) in the table to read. the SQLCODE it returned could be an indication of no data or end of data in the table.

thx