September 19, 2005 at 7:43 am
hi,
I am trying to created join on subquesry and getting trouble with it since there is lots of info to gather. I have query bellow to get join the date and descrip to e.date and e.descrip in subquery.
thatnks
here is the query:
select Lplannum As "Plan Number",Loan_Num as "Loan Number", Branchd as "Branch",
LPLANDESC as "Plan Description", borr_first As "Borrower", OrigD as "Loan Officer",
APPStatd as "Status", Dates as "Status Date", Descrip as "Discription"
from test.mtgterms, test.plandat, test.codes, test.Borrinfo, test.events
where test.plandat.key = test.mtgterms.key
and test.mtgterms.key = test.codes.key
and test.mtgterms.key = test.borrinfo.key
and test.plandat.key = test.events.key
and test.plandat.lplannum = 111
Subquery::
select e.DATES, e.DESCRIP
from
(select key, max(IDX)IDX from test.events where len(descrip) >0 group by key) t1
join test.events e on e.key = t1.key
and e.IDX = t1.IDX
September 19, 2005 at 1:02 pm
The main problem is that you are mixing two syntaxes you need to stick to one.
ex:
select
Lplannum As "Plan Number"
, Loan_Num as "Loan Number"
, Branchd as "Branch"
, LPLANDESC as "Plan Description"
, borr_first As "Borrower"
, OrigD as "Loan Officer"
, APPStatd as "Status"
, Dates as "Status Date"
, Descrip as "Discription"
, e.DATES -- SAME name as above ??
, e.DESCRIP -- SAME name as above ??
from
test.mtgterms
join
test.plandat on test.plandat.key = test.mtgterms.key
join
test.codes on test.mtgterms.key = test.codes.key
join
test.Borrinfo on test.mtgterms.key = test.borrinfo.key
join
test.events on test.plandat.key = test.events.key and test.plandat.lplannum = 111
join
(select key, max(IDX)IDX from test.events where len(descrip) >0 group by key) t1 on test.events.key = t1.key
and test.events.IDX = t1.IDX
* Noel
September 20, 2005 at 10:03 am
You could also make your subquery into a view, and then use the view in the join statement. Views can be indexed to improve performance, if that plays into the problem at all.
September 20, 2005 at 10:12 am
Can't use max in a indexed view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply