아래의 오라클 쿼리가 MS-SQL 로 데이터베이스가 변경될 시에 똑같이
구현이 가능할까요?
아님 프로그램으로 구현을 해야 할까요?
조언 바랍니다.
SELECT company_name, name, email
FROM (
SELECT p.company_name -- 파트너 회사명
,p.staff name -- 담당자명
,p.email_id ||'@'|| p.email_domain email -- 담당자
메일
FROM prm_partner p
, cs_zipcode zip
, (
select partner_id
,replace(LTRIM(replace(
max(decode(field_code , 'HCP',
'HCP'))||
max(decode(field_code , 'HLP', ' /
HLP'))||
max(decode(field_code , 'HSP', ' /
HSP'))||
max(decode(field_code , 'HGP', ' /
HGP'))||
max(decode(field_code , 'HEP', ' /
HEP'))||
max(decode(field_code , 'HOP', ' /
HOP'))
, '/', '')), ' ', ' / ')
mk_field
from prm_partner_market_field
group by partner_id
) fl
$mf_tbl
WHERE p.partner_id = fl.partner_id (+)
AND p.addr_id = zip.objid (+)
$market_field_where
$cond_area
$cond_ceo_name
$cond_company_name
$cond_class
$cond_status
$cond_partner_id
$cond_b_join_date
$cond_e_join_date
--
UNION ALL
--
SELECT pp.company_name -- 파트너 회사명
, pm.name -- 영업담당자 명
, pm.email_id||'@'||pm.email_domain email -- 영업담당자
메일
FROM prm_marketer pm
,prm_partner pp
WHERE pm.partner_id = pp.partner_id
AND pm.active = '1' -- 활동중인 영업담당자만...
AND pm.partner_id IN (
SELECT p.partner_id -- 파트너 아이디
FROM prm_partner p
, cs_zipcode zip
, (
select partner_id
,replace(LTRIM(replace(
max(decode(field_code , 'HCP',
'HCP'))||
max(decode(field_code , 'HLP',
' / HLP'))||
max(decode(field_code , 'HSP',
' / HSP'))||
max(decode(field_code , 'HGP',
' / HGP'))||
max(decode(field_code , 'HEP',
' / HEP'))||
max(decode(field_code , 'HOP',
' / HOP'))
, '/', '')), ' ', ' / ')
mk_field
from prm_partner_market_field
group by partner_id
) fl
$mf_tbl
WHERE p.partner_id = fl.partner_id (+)
AND p.addr_id = zip.objid (+)
$market_field_where
$cond_area
$cond_ceo_name
$cond_company_name
$cond_class
$cond_status
$cond_partner_id
$cond_b_join_date
$cond_e_join_date
) -- end IN
) -- from
GROUP BY company_name,name,email
|