All Forums UDA
marcmc 112 posts Joined 12/05
28 Mar 2008
COUNT and GROUP BY Stmt when SELECT returns zero.

SELECT count(distinct a.claim_ref_id)INTO :v_RepClaimsTWFROM fat_bse_cl_pay_cre_trans aJOIN prt_lu_product bON (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 countGROUP BY a.claim_ref_idHAVING (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)?

Adeel Chaudhry 773 posts Joined 04/08
21 May 2008

SQLCODE as 1 is the indicator for a "warning but successful termination"...HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

SN 77 posts Joined 01/07
21 May 2008

hi,as it returns a 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

You must sign in to leave a comment.