Complex SQL to Access Pass Thru...

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

SQL Server 2000 Forums

Transact-SQL (2000)

Complex SQL to Access Pass Thru...

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2006-01-03 : 22:34:46

I have the following sql code working perfectly in SQL Server 2000 Query Analyzer...


-- Only grab CURR, DELQ, NO_E
select 
	loan.loan_no, 
	loan.loanstat, 
	loan.phone_b_h, 
	loan.phone_b_w, 
	loan.phone_c_h, 
	loan.phone_c_w, 
	loan.ph_b_h_dnc, 
	loan.ph_b_w_dnc, 
	loan.ph_c_h_dnc, 
	loan.ph_c_w_dnc,
	loan.fname,
	loan.lst_chk_dt,
	loan.lname,
	loan.m_b_addr1,
	loan.m_b_city,
	loan.m_b_state,
	loan.m_b_zip,
	LOAN.MSPBANK,
	loan.mspcat,
	loan.NXT_WRK_DT,
	loan.p_city,
	loan.p_state,
	loan.p_zip,
	LOAN.POOL,
	loan.OCC,
	loan.q_delay,
	loan.wrk_strat,
	loan.p_address1
into 
	#loancrit
from 
	loan
where 
	loan.loanstat in ('CURR','DELQ','NO_E','fcfb','fb')

-- Remove loans with DO NOT CALL are code
delete 
from 
	#loancrit
where	
	LEFT(PHONE_B_H,3) IN ('088','099','055','066')
	OR LEFT(PHONE_B_W,3) IN ('088','099','055','066')
	OR LEFT(PHONE_C_H,3) IN ('088','099','055','066')
	OR LEFT(PHONE_C_W,3) IN ('088','099','055','066')

-- Remove numbers with DNC LTS flag
Update 
	#loancrit
SET
	PHONE_B_H = CASE WHEN PH_B_H_DNC = 1 THEN NULL ELSE PHONE_B_H END, 
	PHONE_B_W = CASE WHEN PH_B_W_DNC = 1 THEN NULL ELSE PHONE_B_W END, 
	PHONE_C_H = CASE WHEN PH_C_H_DNC = 1 THEN NULL ELSE PHONE_C_H END, 
	PHONE_C_W = CASE WHEN PH_C_W_DNC = 1 THEN NULL ELSE PHONE_C_W END

-- Remove numbers with bad area codes or phone numbers not 10 digits long
Update
	#loancrit
set
	phone_b_h = case when left(phone_b_h,3) in ('011','033','044','055','066','077','088','099','111','222','333','444','555','666','777','888','999','000','081') or len(phone_b_h) <> 10 then null else phone_b_h end,
	phone_b_w = case when left(phone_b_w,3) in ('011','033','044','055','066','077','088','099','111','222','333','444','555','666','777','888','999','000','081') or len(phone_b_w) <> 10 then null else phone_b_w end,
	phone_c_h = case when left(phone_c_h,3) in ('011','033','044','055','066','077','088','099','111','222','333','444','555','666','777','888','999','000','081') or len(phone_c_h) <> 10 then null else phone_c_h end,
	phone_c_w = case when left(phone_c_w,3) in ('011','033','044','055','066','077','088','099','111','222','333','444','555','666','777','888','999','000','081') or len(phone_c_w) <> 10 then null else phone_c_w end

-- Remove loans without at least one good number
delete
	#loancrit
where 	
	phone_b_h is null and 
	phone_b_w is null and
	phone_c_h is null and
	phone_c_w is null

-- Get attorney only accounts
select 
	attycontact.loan_no,
	max(attycontact.allcalls) as allcalls
into
	#attycrit
from 
	attycontact
group by
	attycontact.loan_no
	
-- Get Bankrupt table information
select 
	bankrupt.loan_no,
	bankrupt.bkfiling,
	bankrupt.bkend,
	BKFLAG = case when BKFILING IS NOT NULL AND (BKEND IS NULL OR BKEND = '1/1/1900') then '1' else '0' end
into
	#bkflagcrit
from 
	bankrupt

-- Get max BKFLAG
select
	#bkflagcrit.loan_no,
	max(BKFLAG) as BKFLAG
into
	#bkflagcrit2
from 
	#bkflagcrit
group by
	#bkflagcrit.loan_no

-- Finds max checkdate on loans
select 	
	#loancrit.loan_no,
	max(currenteasypay.chk_dt) as maxezpaydt
into 
	#maxezpay
from 	
	#loancrit
	left join currenteasypay on currenteasypay.loan_no = #loancrit.loan_no
group by 
	#loancrit.loan_no

-- Finds max 148 date
select 
	#loancrit.loan_no,
	max(msppmt.tran_dt) as last148date
into
	#last148
from	
	#loancrit
	left join msppmt on #loancrit.loan_no = msppmt.loan_no
where
	msppmt.tran_code = '148'
group by 
	#loancrit.loan_no

-- Get latest payoff good through date
select 
	#loancrit.loan_no,
	max(payoff.good_dt) as pogooddate
into 
	#payoffcrit
from
	#loancrit
	left join payoff on #loancrit.loan_no = payoff.loan_no
group by 
	#loancrit.loan_no

-- Get max promise date.
select 
	#loancrit.loan_no,
	max(promise.PTP_DATE) as maxpromisedt
into
	#promisecrit
from
	#loancrit
	left join promise on #loancrit.loan_no = promise.loan_no
group by 
	#loancrit.loan_no

-- Finds max 142 date
select 
	#loancrit.loan_no,
	max(msppmt.tran_dt) as last142date
into
	#last142
from	
	#loancrit
	left join msppmt on #loancrit.loan_no = msppmt.loan_no
where
	msppmt.tran_code = '142'
group by 
	#loancrit.loan_no

-- Final Selection Criteria
select 
	#loancrit.loan_no as ACCOUNT,
	mspbal.autodraft as ACHFLAG,
	convert(char(10),originfo.aquired_dt,101) as ACQDT,
	#attycrit.AllCalls as ATTNYONLY,
	stopcode.bcstop as BDCHCKSTOP,
	convert(char(10),loancodes.dischargebankruptcydate,101) as BKDSCHRGDT,
	loancodes.DischargedBK as dischargedbk,
	#bkflagcrit2.BKFLAG as BKFLAG,
	Null as CBTIME,
	Null as CHCKLGAGENT,
	Null as CHCKLGCRTDT,
	Null as CHCKLGDPT,
	Null as CHCKLGRCVDDT,
	enterprise.dbo.lighthousebase.client as CLIENT,
	enterprise.dbo.lighthousebase.clientid as CLIENTID,
	loan.co_fname as COFNM,
	loan.co_lname as COLNM,
	agents.collect3 as ASSGNDCOLL,
	loan.comp_lts as PRIMSRVCR,
	mspbal.first_prin as CURPRIN,
	Null as LTTRSNTDT,
	DPD = case when mspbal.nextpaydt < getdate() then Datediff(day,mspbal.nextpaydt,getdate()) else null end,
	convert(char(10),foreclos.demand_dt,101) as DMNDDT,
	loan.dflt_cltr as DFLTCLTR,
	PMIHEADER.disb_stop as DISBSTOP,
	EZPFLAG = case when #maxezpay.maxezpaydt >= CAST(CONVERT(VARCHAR(20),GETDATE(),101) AS DATETIME) then '1' else '0' end,
	Null as FCSALEDT,
	LoanScoresCurrent.fico_Score as FICO,
	Null as NOCONTATTMPTS,
	#loancrit.fname as FNM,
	loancodes.b_lang as LANGFLAG,
	convert(char(10),loangroup.lastattempt,101) as LASTATT,
	convert(char(10),#loancrit.lst_chk_dt,101) as LASTCHCKDT,
	convert(char(10),loangroup.lastcont,101) as LASTCNTCTDT,
	convert(char(10),#last148.last148date,101) as LAST148DT,
	Null as LASTWRKDT,
	Null as LETTERTYPE,
	Originfo.LIEN_POS as LIENPOS,
	#loancrit.lname as NAME,
	#loancrit.loanstat as LNSTAT,
	Originfo.type as LNTYPE,
	Originfo.type as LNTYPECODE,
	OutSourcedLoansCurrentStatus.LRD_Flag as LRDFLAG,
	#loancrit.m_b_addr1 as MBADDR1,
	#loancrit.m_b_city as MBCITY,
	#loancrit.m_b_state as MBST,
	#loancrit.m_b_zip as MBZIP,
	loandelq.mba_delq as MBADELQ,
	mspbal.MON_PAY as MNTHLYPYMNT,
	#loancrit.mspbank as MSPBANK,
	#loancrit.mspcat as MSPCAT,
	Null as MSTATUS,
	Null as NXTFBPYMNT, 
	Null as NXTFBPYMNTDT,
	convert(char(10),mspbal.nextpaydt,101) as NXTPAYDT,
	convert(char(10),#loancrit.NXT_WRK_DT,101) as NXTWKDT,
	#loancrit.p_city as PCITY,
	#loancrit.p_state as PSTATE,
	#loancrit.p_zip as PZIP,
	convert(char(10),#payoffcrit.pogooddate,101) as PAYOFFGTDT,
	Null as PENDCLMAMNT,
	#loancrit.phone_b_h as NUMBER,
	#loancrit.phone_b_w as NUMBER1,
	#loancrit.phone_c_h as NUMBER2,
	#loancrit.phone_c_w as NUMBER3,
	#loancrit.pool as POOL,
	stopcode.prcstop as PROCSTOP,
	enterprise.dbo.lighthousebase.producttype as PRODIND,
	#loancrit.occ as PROPOCCCODE,
	convert(char(10),#promisecrit.maxpromisedt,101) as PTPDT,
	PTPFLAG = case when #promisecrit.maxpromisedt >= CAST(CONVERT(VARCHAR(20),GETDATE(),101) AS DATETIME) then '1' else '0' end,
	Null as PVX,
	#loancrit.q_delay as QDELAY,
	LoanScores.Risk_Score as RISKSCR,
	convert(char(10),#last142.last142date,101) as LAST142DT,
	#loancrit.wrk_strat as WRKSTR,
	#loancrit.p_address1 as PADDR1
from 
	#loancrit
	left join mspbal on #loancrit.loan_no = mspbal.loan_no
	left join originfo on #loancrit.loan_no = originfo.loan_no
	left join #attycrit on #loancrit.loan_no = #attycrit.loan_no
	left join stopcode on #loancrit.loan_no = stopcode.loan_no
	left join loancodes on #loancrit.loan_no = loancodes.loan_no
	left join #bkflagcrit2 on #loancrit.loan_no = #bkflagcrit2.loan_no
	left join enterprise.dbo.lighthousebase on mspbal.sanbankcatid = enterprise.dbo.lighthousebase.sanbankcatid
	left join loan on #loancrit.loan_no = loan.loan_no
	left join agents on #loancrit.loan_no = agents.loan_no
	left join foreclos on #loancrit.loan_no = foreclos.loan_no
	left join pmiheader on #loancrit.loan_no = pmiheader.loan_no
	left join #maxezpay on #loancrit.loan_no = #maxezpay.loan_no
	left join LoanScoresCurrent on #loancrit.loan_no = LoanScoresCurrent.loan_No
	left join loangroup on #loancrit.loan_no = loangroup.loan_no
	left join #last148 on #loancrit.loan_no = #last148.loan_no
	left join OutSourcedLoansCurrentStatus on #loancrit.loan_no = OutSourcedLoansCurrentStatus.loan_no
	left join loandelq on #loancrit.loan_no = loandelq.loan_no
	left join #payoffcrit on #loancrit.loan_no = #payoffcrit.loan_no
	left join #promisecrit on #loancrit.loan_no = #promisecrit.loan_no
	left join #last142 on #loancrit.loan_no = #last142.loan_no
	left join loanscores on #loancrit.loan_no = loanscores.loan_no
order by 
	#loancrit.loan_no

drop table 
	#loancrit
drop table
	#attycrit
drop table
	#bkflagcrit
drop table
	#bkflagcrit2
drop table
	#maxezpay
drop table
	#last148
drop table
	#payoffcrit
drop table
	#promisecrit
drop table
	#last142

But when I try to run this as a SQL pass-through query in Access, I get the following message:

"Pass-through query with ReturnsRecords property set to True did not return any records."

I'm new to using sql server...is there something in the above code that will not work in Access as a pass through query? Can someone make any suggestions to improve the above code or make it work?

Thanks in advance for any help provided...

Vassago

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-04 : 03:46:04

One guess would be that you need a SET NOCOUNT ON to prevent access from seeing the blank recordsets returned by the first few queries. I would expect that the problem is caused because you have a number of queries in your batch and only the last returns any results.

-------
Moo. :)

Vassago
Starting Member

33 Posts

Posted - 2006-01-04 : 14:05:55

That worked perfectly! Thanks very much! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources