More than 1 criteria in DECODE?
using pl/sql..
The data I have that the SQL pulls from below consists of a category of records called sub-members. These sub members have a main member associated with them that exists as a separate record as:
member member type
A Sub
B Sub
C Main
My code takes as of the sub members as its 'primary' records and creates another column to place the associated main member in as:
member Main Member
A C
B C
This is accomplished through the DECODE line..
However, there are actually 2 possible (but only one for each set of sub members) member_type categories that can exist: MM1 as shown in the DECODE statement and also FM1.
What I need to know is, is it possible to add FM1 as well as MM1 in the DECODE criteria? I tried to no avail:
max(DECODE(mbr00.mbr00_mbr_type,'MM1 ','FM1 ',mbr00.mbr00_first_name)) AS main_first_name_decode
and also tried using the OR keyword to no avail
Thanks for any assistance!
===============================================================
SQL CODE:
SELECT mbr00.mbr00_subs_ssn,
me607.elig_start_date,
me607.elig_end_date,
me607.subscriber_id,
me607.person_id,
me607.group_id,
me607.last_name,
me607.first_name,
mbr02.mbr02_add_proc_date,
mbr02.mbr02_orig_office,
mbr02.mbr02_app_date,
mbr02.mbr02_orig_eff_date,
max(DECODE(mbr00.mbr00_mbr_type,'MM1 ',mbr00.mbr00_first_name)) AS sub_first_name_decode
from expanded me607,
master mbr00,
enroll_info mbr02
where me607.subscriber_id = mbr00.mbr00_ssn_ref_num
and mbr00.mbr00_subs_ssn = mbr02.mbr02_subs_ssn
and mbr00.mbr00_mbr_type = mbr02.mbr02_mbr_type
and me607.group_id = mbr02.mbr02_grp_id
and me607.group_id = 'ABCDEF'
and me607.person_id not in ('00','16')
Group by
mbr00.mbr00_subs_ssn,
me607.elig_start_date,
me607.elig_end_date,
me607.subscriber_id,
me607.person_id,
me607.group_id,
me607.last_name,
me607.first_name,
mbr02.mbr02_add_proc_date,
mbr02.mbr02_orig_office,
mbr02.mbr02_app_date,
mbr02.mbr02_orig_eff_date
Order by
me607.person_id