ERROR: CLI execute error:(SQL0206N or SQL0408N) . Neither way works

[iren]

I am running code against data warehouse in DB2, trying
to get unique members with at least 2 claims on
different dates

EXECUTE(
create table &tmpschema..ckd
( Id char (29)
,clms_cnt smallint
)
in "&twotblspc"
not logged initially
) by HDMconnect;

EXECUTE(
insert into &tmpschema..ckd
select distinct
Id
,Count(distinct Srv_Dt) as clms_cnt
from &tmpschema..ckd_clm
Group by Hedis_id
having clms_cnt >= 2

) by Hconnect;
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0206N
"CLMS_CNT" is not valid in the context where it is used.
SQLSTATE=42703

************************


Actually when I am trying another way:

EXECUTE(
create table &tmpschema..ckd
( Id char (29)
Srv_Dt date
)
in "&twotblspc"
not logged initially
) by HDMconnect;EXECUTE(

insert into &tmpschema..ckd
select distinct
Id
,Count(Distinct Srv_Dt) as clms_cnt
from &tmpschema..ckd_clm
Group by Hedis_id
having claims_count >= 2

) by Hconnect;


It gave me an error that SQL0408N A value is not
compatible with the data type of its assignment
targetTry as I might I am unable to resolve it.

What I am doing wrong?
Thank you in advance,

Iren

 

[bibulous]

In your fist statement try

having Count(distinct Srv_Dt) >= 2

In your second statement you are trying to store the count in a date field.

Hope this helps.