select
null, '#' || g.SGROUP_ID "SGROUP_ID",
t.IMEMBER_SK, t.SMEMBER_ID, t.SFAMILY_ID, t.ISORT_ID, t.SFAMILYRELATION_ID,
t.SNATIVE_NM || '' || case
when substr (g.SGROUP_ID,4,1) = '1'and substr (g.SGROUP_ID,5,1) is null and t.GROUP_SORT_KEY = 'Y'
then ' <htmL><b><font color="Red"> *** </font></b></html>'
when t.GROUP_LEADER_KEY = 'Y' then ' <htmL><b><font color="Red"> ** </font></b></html>'
when t.GROUP_SORT_KEY = 'Y' then '<htmL><b><font color="Red"> * </font></b></html>' else ' ' end SNATIVE_NM,
case when substr (t.STELPERSONAL, 1,3) = '111' then '<htmL><blink><font color="Red">Not Provided</font></blink></html>' else to_char (t.STELPERSONAL) end as STELPERSONAL,
case when t.IBIRTH_DT = 0 or t.IBIRTH_DT = 11111111 then '<htmL><blink><font color="Red">Not Provided</font></blink></html>' else to_char (substr(t.IBIRTH_DT,5,2)) || '/' || to_char (substr(t.IBIRTH_DT,7,2)) || '/' || to_char (substr(t.IBIRTH_DT,1,4)) END as "IBIRTH_DT",
CASE when t.STITLE = '' or substr(t.STITLE,1,1) = '%' then '-' ELSE to_char (t.STITLE ) END AS Title, t.ILEADMEMBER_SK , tt.SNATIVE_NM as Leader
from TBLCHURCHMEMBER t, TBLGROUP g, TBLCHURCHMEMBER tt
where
t.GROUP_CELL_FLAG = 'Y'
and substr(t.SMEMBER_ID,6,2) in ('HD','SP','P1','P2')
and t.ICELLGROUP_SK = g.IGROUP_SK
and substr( g.SGROUP_ID,1,1) != '9'
and t.ILEADMEMBER_SK = tt.IMEMBER_SK
order by "SGROUP_ID", t.GROUP_SORT_KEY, "SFAMILY_ID", "SFAMILYRELATION_ID")
