help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help

Author

Topic

what1980
Starting Member

2 Posts

Posted - 2010-12-09 : 15:50:40

Ive never seen something like this. Any help would be great. (especially the select within the select)

CURSOR call_center_cur IS
select distinct ds.patient_id
,ds.therapy_type
,(select pth.TRC_VALUE
from THOT.PT_TRC_PHONES_HDR pth, THOT.PT_TRC_PHONES_DTL ptd
where pth.trc_id=ptd.trc_id
and ptd.therapy_type=ds.therapy_type) TRC
,ds.session_id
,trunc(ds.tm_ident_code) tm_ident_code
,dac.category_code
,das.section_code
from ADM.DM_SESSION_ASSESSMENTS dsa
,ADM.DM_SESSIONS ds
,ADM.DM_ADMIN_SECTIONS das
,ADM.DM_ADMIN_CATEGORIES dac
,patient_insurance pi
,patients_table p
where pi.claim_center_no in (select g_num_value from groupr where type='CC' and name = '&&TEXT1')
and (pi.effective_date <= '&&2' or pi.effective_date is null)
and (pi.expiration_date >= '&&1' or pi.expiration_date is null)
--and pi.claim_center_seq=(select min(pi2.claim_center_seq) from patient_insurance pi2
-- where pi2.patient_id=pi.patient_id
-- and pi2.claim_center_no=pi.claim_center_no)
and pi.patient_id=ds.patient_id
and dsa.session_id = ds.session_id
AND dsa.section_id = das.section_id
AND dsa.category_id = dac.category_id
and ds.excluded='N'
and trunc(ds.tm_ident_code) between '&&1' and '&&2'
and (dac.category_code='NCSC' and das.section_code in ('BDQSYTERECALL','INEBRELATEDCALLS','NCSC_ACCESS','NCSC_CARE_CALLS','NCSC_FAXREC','NCSC_GENERAL','NCSC_INEB','NCSC_MCCPAYOR','NCSC_PUMP','QUESTIONABLE_CAS_RET','TYINHDEVICECALL')
or dac.category_code='MULTIPLE SCLEROSIS' and das.section_code in ('REPHARMACISTREFILL','RERPHMDCONTACT')
or dac.category_code='PAHFLOLAN' and das.section_code in ('PAHGFLO_CALL_REFER')
or dac.category_code='PAHREMODULIN' and das.section_code in ('PAHREMO_CALL_INITREF')
)
and pi.patient_id=p.id
and not exists (select 1
from entity_regions ers
where ers.source_id = p.svcbr_id
and ers.region_type = 'AREA'
and ers.region like 'CCS%');
rec_call_center_cur call_center_cur%ROWTYPE;

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 16:12:53

Doesn't look like MS SQL. oracle perhaps?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources