Larry Brister
asked on
Very complex cross database select statement
In my select statement below I need the ability to change the database I'm selecting from on a record by record basis.
So...when Left(p.afsSource,2) = 'LT'
I pull from proc_lt.dbo.P_PaymentStrea ms
Else
I pull from crmprod_01.dbo.P_PaymentSt reams
The top part will ALWAYS pull from proc_cfa.dbo.P_AvailableFo rSale
So...when Left(p.afsSource,2) = 'LT'
I pull from proc_lt.dbo.P_PaymentStrea
Else
I pull from crmprod_01.dbo.P_PaymentSt
The top part will ALWAYS pull from proc_cfa.dbo.P_AvailableFo
Declare @OrdTranID int
Set @OrdTranID = 9001
Select Left(p.afsSource,2) as busType,
SubString(p.afsSource,4,4) as DealID,
Sum(p.afsAMount) as afsAmount,
st.cp as CompoundingMethod
from proc_cfa.dbo.P_AvailableForSale p
LEFT JOIN (
Select
ps.DealID
,CASE WHEN COUNT(ps.PmtFrequency) > 1 THEN 'Split' ELSE MIN(ps.PmtFrequency) END cp
,MAX(ps.loanDate) LoanDate
from proc_lt.dbo.P_PaymentStreams ps
Group by ps.DealID
) st ON st.DealId = SubString(p.afsSource,4,4)
where p.afsTransAction = @OrdTranID
group by Left(p.afsSource,2), SubString(p.afsSource,4,4), st.cp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anything you ever need...mayhem, assault, robbery...just let me know
:-D