COUNT and GROUP BY Stmt when SELECT returns zero.

Visit Teradata.com

Home

Guidelines

Member List

Welcome Guest ( Login | Register )

This online forum is for user-to-user discussions of Teradata products, and is not an official customer support channel for Teradata. If you require direct assistance, please contact Teradata support.

COUNT and GROUP BY Stmt when SELECT returns...

Rate Topic

Display Mode

Topic Options

Author

Message

marcmc

Posted 3/28/2008 10:36:42 AM

Supreme Being

Group: Forum Members
Last Login: 4/3/2008 12:29:54 PM
Posts: 102, Visits: 81

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)?

Post #11083

Adeel.Chaudhry

Posted 5/21/2008 8:20:26 AM

Supreme Being

Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134, Visits: 370

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

HTH!

Regards,

Adeel

Post #11527

Posted 5/21/2008 1:49:59 PM

Supreme Being

Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76, Visits: 147

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

-SN

Post #11536

« Prev Topic | Next Topic »

Reading This Topic

Active Users: 0 ( 0 guests, 0 members, 0 anonymous members )

No members currently viewing this topic.

All times are GMT -5:00, Time now is 11:43pm