SP with CASE 3

[cristi22]

Hello all -

DB2 8.2

The coverage_code could be Single, Family or Both.
How can I add the code for both ?

I also have another cursor for the employer. The user can enter the employer number or it could be NULL

Here is my SP

......

Declare cursor1 cursor with return for

        Select
          member_ssn
        , member_name_first
        , member_initial
        , member_name_last
        , member_sex
        --, hire_date
        --, last_term_date
        , ben_date
        , benefit_plan
        , end_date
        , employer
        , empl_name

        from umass_empl , elg_summary
        where
                coverage_code = ( case when ucase(in_coverage) = 'SINGLE' then 'C'else 'A' end ) 
                and ben_date = in_eff_date
                and benefit_plan =  ucase(in_ben_plan)
                and dep_ssn='' and dep_name=''
                and employer = empl
        order by 1;


declare cursor 2....
.............

Thanks all for help!

 

[rbod]

case when ucase(in_coverage) = 'SINGLE' then 'C'
when ucase(in_coverage) = 'FAMILY' then 'B'
else 'A' end )

 

[MarcLodge]

Hi Cristi22,
What values are you expecting to find in coverage_code? You have said that this can be "Single, Family, or Both", but then in your SQL you are only checking for the values 'C' or 'A'.

What values do you expect to find in in_coverage, and what do you wish to convert them to so that coverage_code finds them?

The case statement should be adjusted:

case ucase(in_coverage)
when 'SINGLE' then 'C'
when 'FAMILY' then '?'
when 'BOTH' then '?'
end case

replacing the ? with the correct values.

Hope this helps.

Marc
then 'C'else 'A' end )

 

[cristi22]

thanks all for you help!

but i really need something like this:

case ucase(in_coverage) 
 when 'SINGLE' then 'C'
 when 'FAMILY' then 'A'
 when 'BOTH'   then 'A' & 'C' ?????????
end case

 

[rbod]

case ucase(in_coverage)
when 'SINGLE' then 'C'
when 'FAMILY' then 'A'
when 'BOTH' then 'A,C'
end case

 

[ddiamond]

Are you saying that if they enter 'BOTH' than it could be either A or C? Although using OR operators in your where clause can be inefficient, you could try the following:

Declare cursor1 cursor with return for

        Select
          member_ssn
        , member_name_first
        , member_initial
        , member_name_last
        , member_sex
        --, hire_date
        --, last_term_date
        , ben_date
        , benefit_plan
        , end_date
        , employer
        , empl_name

        from umass_empl , elg_summary
        where
                ((coverage_code = 'C' and in_coverage = 'SINGLE') OR
                 (coverage_code = 'A' and in_coverage = 'FAMILY') OR
                 (coverage_code in ('C','A') and in_coverage = 'BOTH'))
                and ben_date = in_eff_date
                and benefit_plan =  ucase(in_ben_plan)
                and dep_ssn='' and dep_name=''
                and employer = empl
        order by 1;

If performance is a problem, you could try creating a different where clause for each case. See Murcury2's post on thread thread178-1095630.

 

[cristi22]

REALLY, APPRECIATE YOUR HELP ALL!
here is what i've got

  from umass_empl , elg_summary
        where   coverage_code = (
                 case ucase(in_coverage)
                     when 'SINGLE' then 'C'
                     when 'FAMILY' then 'A'
                     when 'BOTH'   then 'A,C'
                end)
                and ben_date = in_eff_date
                and benefit_plan =  ucase(in_ben_plan)
                and dep_ssn='' and dep_name=''
                and employer = empl
        order by 1;

does not return any results ? Any ideas why?

            from umass_empl , elg_summary
        where
                ((coverage_code = 'C' and in_coverage = 'SINGLE') OR
                 (coverage_code = 'A' and in_coverage = 'FAMILY') OR
                 (coverage_code in ('C','A') and in_coverage = 'BOTH'))
                and ben_date = in_eff_date
                and benefit_plan =  ucase(in_ben_plan)
                and dep_ssn='' and dep_name=''
                and employer = empl
        order by 1;

works JUST FINE!