INNER JOIN WITH MULTIPLE TABLES IN SP 1

[Ann28]

Hello everyone –

Any help is appreciated!!!

I need to join another table to the following SP with extracts the data from one table based on the change of coverage.
I need to add the employer name that comes from a diffirent table
I tried to JOIN by did not work

Here is my SP:

CREATE PROCEDURE BASYS.UMASS ( IN IN_FROM_DATE DATE, IN_NEW_BEN_PLAN CHAR (6), IN_OLD_BEN_PLAN CHAR (6) )
    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

[COLOR=RED] ????         --, e.empl_name [/COLOR RED]
    from elg_summary h, elg_summary s

[COLOR=RED] ???
--    inner join (select empl_name from UMASS_EMPL e) as e1 on h.EMPLOYER = e.empl [/COLOR RED]

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;
    -- Cursor left open for client application
    OPEN cursor1;
    end p1

THANKS
ANN

 

[ddiamond]

Try the following for your from clause:

from elg_summary h
  inner join elg_summary s on 
    s.dep_ssn=h.dep_ssn and
    s.dep_name=h.dep_name
  inner join UMASS_EMPL e on h.EMPLOYER = e.empl

 

[Ann28]

Here is the code I've code

   , e1.empl_name
    from elg_summary h
  inner join (select empl, empl_name from UMASS_EMPL) as e1 on h.EMPLOYER = e1.empl ,
  elg_summary s

cheers!
Ann

 

[ddiamond]

The problem is your comma following the inner join. I do not think you can mix commas and inner joins in the same from clause.

    from elg_summary h
  inner join (select empl, empl_name from UMASS_EMPL) as e1 on h.EMPLOYER = e1.empl [b][red],
  elg_summary s [/red][/b]

The sub-query really is not necessary, but if you would like to use it, your code would be

from elg_summary h
  inner join (select empl, empl_name from UMASS_EMPL) as e1 on h.EMPLOYER = e1.empl [blue]
  inner join elg_summary s on 
    s.dep_ssn=h.dep_ssn and
    s.dep_name=h.dep_name [/blue]

Alternatively without the sub-query

from elg_summary h
  inner join [blue]UMASS_EMPL[/blue] as e1 on h.EMPLOYER = e1.empl [blue]
  inner join elg_summary s on 
    s.dep_ssn=h.dep_ssn and
    s.dep_name=h.dep_name [/blue]

 

[Ann28]

thank you for your help!