While executing the below query iam getting error as "Add explicit type casts". Kindly help

Database
Enthusiast

While executing the below query iam getting error as "Add explicit type casts". Kindly help

sql error.JPG

select account_id,product_id,sum(rx.positive),sum(rx.negative)
from(select
account_id,product_id,
case when rx_formula in ('approved','unrestricted') then '1' else'0' end as positive ,
case when rx_formula not in ('approved','unrestricted') then '1' else'0' end as negative
from status)rx
group by 1,2

3 REPLIES
Senior Apprentice

Re: While executing the below query iam getting error as "Add explicit type casts". Kindl

Hint: Your CASE-statements return a character instead of a number.

Enthusiast

Re: While executing the below query iam getting error as "Add explicit type casts". Kindl

Hi Dnoeth,

It's very urgent reqirement,could you please help us....

Thanks in advance...

 

1. GROUP records FROM MISSION_CASE_HIST based ON the same MISSION_CASE_ID , MCID AND MBR_CASE_ID ORDER BY MISSION_CASE_ENTRY_DTM

         a. EACH  record would have TO be examined IN this ORDER:

          b. Episode/record 1 :The very  FIRST record WHERE  MISSION_CASE_HIST .CP_STTS_CD  = 'OPEN' would be the FIRST episode FOR that GROUPING (USE  MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM  FOR PM_STRT_DT)

          c. NEXT Episode / record FOR the same GROUPING :

                 EACH subsequent record FOR the same GROUP WITH  MISSION_CASE_HIST .CP_STTS_CD  = 'OPEN'

                  (AND ONLY IF this record  FOLLOWING the record WITH MISSION_CASE_HIST .CP_STTS_CD  <> 'OPEN' )    would also CREATE an episode(USE  CAST(MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM AS DATE)   FOR  PM_STRT_DT)

 

Enthusiast

Re: While executing the below query iam getting error as "Add explicit type casts". Kindl

Thanks Dnoeth. Got it