STORED PROCEDURE WITH INPUT PARMS 1

[Ann28]

Hello everyone!

Using DB2 8.2 vers on aix.

Really appreciate your help!

Trying to create a SP with input parms.
The first part is the SELECT statement.

(The member could change the coverage from DOHIGH to DOSTAN & other way around)

The user enters the date & BENEFIT_PLAN parameters.
The following is an expample of the data:

example of the INPUT data:

MEMBER_SSN BENEFIT_PLAN BEN_DATE END_DATE   
---------- ------------ ---------- ---------- 
99999999  DOHIGH       07/01/2005 08/31/2005 
99999999  DOSTAN       01/01/2003 06/30/2005 
88888888  DOHIGH       07/01/2005 08/31/2005 
88888888  DOHIGH       01/01/2003 06/30/2005 
77777777  DOSTAN       07/01/2005 08/31/2005 
77777777  DOHIGH       01/01/2003 06/30/2005

PARMS: DATE: DATE = 2005-07-01, Option: DOHIGH TO DOSTAN

????? How do I incorporate these two selects into one statement so the result will be:



Member 99999999 changed: DOHIGH on 07/01/2005 from DOSTAN on 06/30/2005

SELECT  DISTINCT MEMBER_SSN  ,  BENEFIT_PLAN, 
date (  SUBSTR ( BEN_DATE,1,4) || '-' ||  SUBSTR(BEN_DATE, 5,2)  || '-'  ||  SUBSTR (BEN_DATE,7,2 )   ) AS BEGIN_DATE,
date (  SUBSTR ( END_DATE,1,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  SUBSTR (END_DATE,7,2 )   ) AS END_DATE,
from elg_summary   
WHERE    
(MONTH(DATE(SUBSTR(BEN_DATE,1,4) || '-' || SUBSTR(BEN_DATE, 5,2) || '-'  || SUBSTR(BEN_DATE,7,2))) =
MONTH (2005-07-01))
AND 
BENEFIT_PLAN = 'DOHIGH'  



select   DISTINCT MEMBER_SSN  ,  BENEFIT_PLAN, 
date (  SUBSTR ( BEN_DATE,1,4) || '-' ||  SUBSTR(BEN_DATE, 5,2)  || '-'  ||  SUBSTR (BEN_DATE,7,2 )   ) AS BEGIN_DATE,
date (  SUBSTR ( END_DATE,1,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  SUBSTR (END_DATE,7,2 )   ) AS END_DATE,
from elg_summary   
WHERE 
BENEFIT_PLAN =  'DOSTAN'   
AND 
MONTH (2005-07-01)  - 1 ) =
MONTH(date(SUBSTR(END_DATE,1,4) || '-' ||  SUBSTR(END_DATE, 5,2)  || '-'  ||  SUBSTR (END_DATE,7,2 ))) =

THANKS A LOT
Ann

 

[Ann28]

here it is :
Any suggestions on how to make it better?

SELECT  DISTINCT  
HIGH.MEMBER_SSN  
,   HIGH.BENEFIT_PLAN 
,   date (  SUBSTR (  HIGH.BEN_DATE,1,4) || '-' ||  SUBSTR( HIGH.BEN_DATE, 5,2)  || '-'  ||  SUBSTR ( HIGH.BEN_DATE,7,2 )   ) AS HIGH_BEGIN_DATE
,   date (  SUBSTR (  HIGH.END_DATE,1,4) || '-' ||  SUBSTR( HIGH.END_DATE, 5,2)  || '-'  ||  SUBSTR ( HIGH.END_DATE,7,2 )   ) AS HIGH_END_DATE
,  date (  SUBSTR (  STD.BEN_DATE,1,4) || '-' ||  SUBSTR( STD.BEN_DATE, 5,2)  || '-'  ||  SUBSTR ( STD.BEN_DATE,7,2 )   ) AS STD_BEGIN_DATE
,  date (  SUBSTR (  STD.END_DATE,1,4) || '-' ||  SUBSTR( STD.END_DATE, 5,2)  || '-'  ||  SUBSTR ( STD.END_DATE,7,2 )   ) AS STD_END_DATE 
FROM elg_summary  HIGH INNER JOIN elg_summary STD ON STD.MEMBER_SSN =   HIGH.MEMBER_SSN
WHERE 
(
( MONTH(DATE(SUBSTR( HIGH.BEN_DATE,1,4) || '-' || SUBSTR( HIGH.BEN_DATE, 5,2) || '-'  || SUBSTR( HIGH.BEN_DATE,7,2))) = MONTH ('2005-07-01') ) 
AND  HIGH.BENEFIT_PLAN = 'DOHIGH'  
)
AND
(
( MONTH(DATE(SUBSTR( STD.END_DATE,1,4) || '-' || SUBSTR( STD.END_DATE, 5,2) || '-'  || SUBSTR( STD.END_DATE,7,2))) = MONTH ('2005-07-01') - 1 ) 
AND  STD.BENEFIT_PLAN = 'DOSTAN'  
)

 

[ddiamond]

Ann,

In your examples, the start date of the plan switch is always one day after the end date of the original plan. Is this always the case, or could there be a gap in coverage? Are you interested in writing a query that finds the dates of the switch even if there is a gap?

- Dan

 

[Ann28]

Dan -

The condition is based on the benefit’s date month.
The end day of the previous coverage should be one month less than the beginning month of the current coverage

My SP is set-up to handle the DOHIGH input parameter.

SP:

CREATE PROCEDURE BASYS.UMASS ( IN FROM_DATE DATE, BENEFIT_PLAN CHAR (6) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- IN_OPTION
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

DECLARE cursor1 CURSOR WITH RETURN FOR

    SELECT  DISTINCT
      HIGH.MEMBER_SSN
    , HIGH.BENEFIT_PLAN
    , HIGH.BEN_DATE
    , HIGH.END_DATE
    , STD.BENEFIT_PLAN
    , STD.BEN_DATE
    , STD.END_DATE
    FROM
    (
    SELECT
        MEMBER_SSN
        , BENEFIT_PLAN
        , BEN_DATE
        , END_DATE
        FROM ELG_SUMMARY
        WHERE  BENEFIT_PLAN = 'DOHIGH'
     ) AS HIGH
INNER JOIN
(
    SELECT
    MEMBER_SSN
    , BENEFIT_PLAN
    , BEN_DATE
    , END_DATE
    FROM ELG_SUMMARY
    WHERE  BENEFIT_PLAN = 'DOSTAN'
) AS STD
ON HIGH.MEMBER_SSN  = STD.MEMBER_SSN
[b] [COLOR=red]WHERE
(
        MONTH (  HIGH.BEN_DATE  )  = MONTH ( FROM_DATE )  AND YEAR ( HIGH.BEN_DATE  ) = YEAR ( FROM_DATE )
        AND  HIGH.BENEFIT_PLAN = 'DOHIGH'
)
AND
(
        MONTH ( STD.END_DATE)   =   MONTH ( FROM_DATE )    - 01  AND YEAR ( STD.END_DATE  ) = YEAR ( FROM_DATE )
        AND  STD.BENEFIT_PLAN = 'DOSTAN'
); [/color red][/b] 
    -- Cursor left open for client application
    OPEN cursor1;
END P1

Now, I need to incorporate the DOSTAN input parameter in the WHERE clause somehow (pretty much the same as HIGH)

Something like

WHERE
(
??? CASE BENEFIT_PLAN  = ‘DOHIGH’ THEN
        MONTH (  HIGH.BEN_DATE  )  = MONTH ( FROM_DATE )  AND YEAR ( HIGH.BEN_DATE  ) = YEAR ( FROM_DATE )
        AND  HIGH.BENEFIT_PLAN = 'DOHIGH'
)
AND
(
        MONTH ( STD.END_DATE)   =   MONTH ( FROM_DATE )    - 01  AND YEAR ( STD.END_DATE  ) = YEAR ( FROM_DATE )
        AND  STD.BENEFIT_PLAN = 'DOSTAN'
); 
??? CASE BENEFIT_PLAN = 'DOSTAN' THEN
        MONTH (  HIGH.BEN_DATE  )  = MONTH ( FROM_DATE )  AND YEAR ( HIGH.BEN_DATE  ) = YEAR ( FROM_DATE )
        AND  HIGH.BENEFIT_PLAN = 'DOSTAN'
)
AND
(
        MONTH ( STD.END_DATE)   =   MONTH ( FROM_DATE )    - 01  AND YEAR ( STD.END_DATE  ) = YEAR ( FROM_DATE )
        AND  STD.BENEFIT_PLAN = 'DOHIGH'
);

THANKS,
ANN

 

[ddiamond]

Okay,

The following query takes a slightly different approach. Rather than doing messy month arithmetic which has a lot of special cases, it uses an exists clause to find 2 plans that are different, but right next to each other. It does not ensure that they are only a month apart, but it does ensure that they are one right after the other with nothing in between. You can replace 'DOHIGH TO DOSTAN' and '20050701' with input parameters. It assumes the input date is in the form YYYYMMDD. If not you will have to reformat. I have omitted the select clause because it has not changed.

select ...
from elg_summary HIGH INNER JOIN elg_summary STD ON 
  STD.MEMBER_SSN = HIGH.MEMBER_SSN
where 1=1
  and high.benifit_plan = 
    (Case when 'DOHIGH TO DOSTAN' 
       then 'DOHIGH' 
       else 'DOSTAN' end)
  and std.benifit_plan <> high.benifit_plan
  and high.ben_date = '20050701' 
  and std.ben_date < high.ben_date 
  and not exists (
    select *
    from elg_summary std2
    where std2.member_ssn = std.member_ssn 
      and std2.ben_date between std.ben_date and high.ben_date );

 

[Ann28]

where 1=1 what does it mean?

Thank you for your help
I've changed the code (it works!!!)
There are few more things I have to add:
1. The user requested to do the search by Employer
(Enter one employer if none is entered SELECT * )
2. Add the employer NAME
I created a table with EMPL# & NAME

Any help is really appreciated!

CREATE PROCEDURE BASYS.UMASS ( IN IN_FROM_DATE DATE, IN_NEW_BEN_PLAN CHAR (6), IN_OLD_BEN_PLAN CHAR (6), IN_EMPL CHAR (9) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- IN_OPTION
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

DECLARE cursor1 CURSOR WITH RETURN FOR
  select h.member_ssn, h.benefit_plan, h.ben_date, h.end_date, s.benefit_plan, s.ben_date, s.end_date,
         h.MEMBER_NAME_FIRST, h.MEMBER_INITIAL, h.MEMBER_NAME_LAST, h.MEMBER_SEX,
         h.EMPLOYER, h.HIRE_DATE, h.LAST_TERM_DATE 
from elg_summary h, elg_summary s
where s.member_ssn=h.member_ssn
and s.dep_ssn=h.dep_ssn
and s.dep_name=h.dep_name
and h.dep_ssn='' and h.dep_name=''
and h.benefit_plan= UCASE (IN_NEW_BEN_PLAN) and s.benefit_plan= UCASE(IN_OLD_BEN_PLAN)
and h.ben_date= date(IN_FROM_DATE) and h.end_date>=date(IN_FROM_DATE)
and s.end_date=(h.ben_date - 1 day)
and  h.EMPLOYER = IN_EMPL
order by 1;
    -- Cursor left open for client application
    OPEN cursor1;
END P1

thanks,
Ann

 

[Ann28]

I've got the code for the null input parms from the earlier posts from Ddiamond
I am getting an error on variable declaration

CREATE PROCEDURE BASYS.UMASS ( IN IN_FROM_DATE DATE, IN_NEW_BEN_PLAN CHAR (6), IN_OLD_BEN_PLAN CHAR (6), IN_EMPL CHAR (9) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- IN_OPTION
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

DECLARE cursor1 CURSOR WITH RETURN FOR
  select h.member_ssn, h.benefit_plan, h.ben_date, h.end_date, s.benefit_plan, s.ben_date, s.end_date,
         h.MEMBER_NAME_FIRST, h.MEMBER_INITIAL, h.MEMBER_NAME_LAST, h.MEMBER_SEX,
         h.EMPLOYER, h.HIRE_DATE, h.LAST_TERM_DATE
    from elg_summary h, elg_summary s
    where s.member_ssn=h.member_ssn
    and s.dep_ssn=h.dep_ssn
    and s.dep_name=h.dep_name
    and h.dep_ssn='' and h.dep_name=''
    and h.benefit_plan= UCASE (IN_NEW_BEN_PLAN) and s.benefit_plan= UCASE(IN_OLD_BEN_PLAN)
    and h.ben_date= date(IN_FROM_DATE) and h.end_date>=date(IN_FROM_DATE)
    and s.end_date=(h.ben_date - 1 day)
    order by 1;

DECLARE cursor2 CURSOR WITH RETURN FOR
  select h.member_ssn, h.benefit_plan, h.ben_date, h.end_date, s.benefit_plan, s.ben_date, s.end_date,
         h.MEMBER_NAME_FIRST, h.MEMBER_INITIAL, h.MEMBER_NAME_LAST, h.MEMBER_SEX,
         h.EMPLOYER, h.HIRE_DATE, h.LAST_TERM_DATE
    from elg_summary h, elg_summary s
    where s.member_ssn=h.member_ssn
    and s.dep_ssn=h.dep_ssn
    and s.dep_name=h.dep_name
    and h.dep_ssn='' and h.dep_name=''
    and h.benefit_plan= UCASE (IN_NEW_BEN_PLAN) and s.benefit_plan= UCASE(IN_OLD_BEN_PLAN)
    and h.ben_date= date(IN_FROM_DATE) and h.end_date>=date(IN_FROM_DATE)
    and s.end_date=(h.ben_date - 1 day)
    and  h.EMPLOYER = IN_EMPL
    order by 1;

[COLOR=red]Declare p_in_empl VARCHAR(9); [/color]
    set p_in_empl = in_empl;

    If p_in_empl is Null then
      OPEN cursor1;
    ELSE
      OPEN cursor2;
    END IF;
    -- Cursor left open for client application

END p1

error:

BASYS.UMASS: 39: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "<variable declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=39. SQLSTATE=42601

THANKS FOR YOUR HELP,
ANN

 

[ddiamond]

Ann,

Move Declare p_in_empl VARCHAR(9); before the cursor declarations.

CREATE PROCEDURE BASYS.UMASS ( IN IN_FROM_DATE DATE, IN_NEW_BEN_PLAN CHAR (6), IN_OLD_BEN_PLAN CHAR (6), IN_EMPL CHAR (9) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- IN_OPTION
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

[red]Declare p_in_empl VARCHAR(9); [/red]

DECLARE cursor1 CURSOR WITH RETURN FOR
  select h.member_ssn, h.benefit_plan, h.ben_date, h.end_date, s.benefit_plan, s.ben_date, s.end_date,
         h.MEMBER_NAME_FIRST, h.MEMBER_INITIAL, h.MEMBER_NAME_LAST, h.MEMBER_SEX,
         h.EMPLOYER, h.HIRE_DATE, h.LAST_TERM_DATE
    from elg_summary h, elg_summary s
    where s.member_ssn=h.member_ssn
    and s.dep_ssn=h.dep_ssn
    and s.dep_name=h.dep_name
    and h.dep_ssn='' and h.dep_name=''
    and h.benefit_plan= UCASE (IN_NEW_BEN_PLAN) and s.benefit_plan= UCASE(IN_OLD_BEN_PLAN)
    and h.ben_date= date(IN_FROM_DATE) and h.end_date>=date(IN_FROM_DATE)
    and s.end_date=(h.ben_date - 1 day)
    order by 1;

DECLARE cursor2 CURSOR WITH RETURN FOR
  select h.member_ssn, h.benefit_plan, h.ben_date, h.end_date, s.benefit_plan, s.ben_date, s.end_date,
         h.MEMBER_NAME_FIRST, h.MEMBER_INITIAL, h.MEMBER_NAME_LAST, h.MEMBER_SEX,
         h.EMPLOYER, h.HIRE_DATE, h.LAST_TERM_DATE
    from elg_summary h, elg_summary s
    where s.member_ssn=h.member_ssn
    and s.dep_ssn=h.dep_ssn
    and s.dep_name=h.dep_name
    and h.dep_ssn='' and h.dep_name=''
    and h.benefit_plan= UCASE (IN_NEW_BEN_PLAN) and s.benefit_plan= UCASE(IN_OLD_BEN_PLAN)
    and h.ben_date= date(IN_FROM_DATE) and h.end_date>=date(IN_FROM_DATE)
    and s.end_date=(h.ben_date - 1 day)
    and  h.EMPLOYER = IN_EMPL
    order by 1;

    set p_in_empl = in_empl;

    If p_in_empl is Null then
      OPEN cursor1;
    ELSE
      OPEN cursor2;
    END IF;
    -- Cursor left open for client application

END p1

 

[Ann28]

I did, it build w/o error, but does not work for the null parm

Thanks all for your help,
Ann

 

[ddiamond]

Is in_empl actually null, or is it an empty string? How are you calling the stored proc?

 

[Ann28]

in CR, but i am testing it first on the command line or running it on the server
thanks

 

[ddiamond]

What happens when you call it with a null param?

 

[Ann28]

no results

 

[ddiamond]

If you run cursor1's select statement by itself, does it return anything?

 

[Ann28]

I set the parms to NULL it works
THANKS FOR YOUR HELP,
Ann