select a.component_id,a.coverage_prefix,a.coverage_code,m d.prefix,
md.se_id,md.se_rule, pd.ctr,pd.cov_pct,pd.cop_pct
from db2inst1.bt a,db2inst1.msd md
left outer join db2inst1.prd pd on (md.se_id=pd.service_id) and
(md.se_rule=pd.service_rule)
where component_id='COTFND'
and a.coverage_prefix=md.prefix
and a.coverage_code=md.se_id
and a.admin_info='MS'
and (md.se_id,md.se_rule) in
(select service_id,service_rule from db2inst1.prd);
Bascially, I want to retrieve information from prd, for every
service_rule matching to the coverage_code.
bt
--
admin_info
coveage_code (se_id)
coverage_prefix
msd
---
prefix
e_id
se_rule
prd
---
service_id
service_rule
ctr
cov_pct
cop_pct
For component of COTFND,
bt record, admin_info='MS',coverage_code=IN, prefix=3110
For prefix of 3110 in msd:
se_id se_rule
=============
IN NCM
NC NCM
CC NCM
CPR NCM
DR 483
ACA 001
NN NCM
MD 026
1. How do I match se_id of IN received from bt record, to get the
se_rule, and in turn
match back to get all se_id's that has the se_rule of NCM, and get the
details from prd for
all se_id's(serive_id's in prd).
2. Also, for values of admin_info of PL/PD, I just want to get the
details from pld/pdd
by not going into msd and prd. How do I achieve all these in one query?
Thanks for the help!