Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

More than 1 criteria in DECODE?

user12296489Feb 18 2010 — edited Feb 22 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 22 2010
Added on Feb 18 2010
22 comments
2,050 views