Liveware Publishing Support Forum

I can create a single Left Outer Join, however I am having trouble creating multiple left outer joins. The Select statement below sample B, work when I pass it in my application however I have only been able to do sample A in R&R____sample A__select Prospect.prospect_id,__LType.meaning LMeaning __ from Calls, {oj Prospect __ left outer join lookups LType__on Prospect.living_type = LType.lookup_code }__ where Prospect.l_city like ^Warren^__ and Calls.Prospect_id = Prospect.Prospect_id____sampele B__select Prospect.prospect_id,__LType.meaning LMeaning,__BType.Meaning BMeaning, __IType.Meaning IMeaning,__CType.Meaning CMeaning, __UType.Meaning UType, __MType.Meaning MType,__campus.description CampsDesc __ from Calls, Prospect__left join lookups LType__on Prospect.living_type = LType.lookup_code and LType.Lookup_type = ^Living Type^ __left join lookups BType__on Prospect.building_type = BType.Lookup_Code and BType.Lookup_Type = ^Building Type^__left join lookups IType__on Prospect.inquiring_for = IType.Lookup_Code and IType.Lookup_Type = ^Inquiring For^__left join lookups CType__on Prospect.currently = CType.Lookup_Code and CType.Lookup_Type = ^Currently^__left join lookups UType__on Prospect.unit_type = UType.Lookup_Code and UType.Lookup_Type = ^Unit Type^__left join lookups MType__on Prospect.must_sell = MType.Lookup_Code and MType.Lookup_Type = ^Must Sell^__left join campus__on Prospect.campus = campus.campus__ where Calls.Prospect_id = Prospect.Prospect_id__

I^m aboout 99% sure you can^t do more than one left outer join.

I have had this problem with multiple left outer joins and have found a way to do very complex sql (Microsoft sql 7 and 2000) with R&R. ____When I cannot get R&R to recognize my Local syntax I wrap the whole statement in an EXEC ____ie__Exec(^select __a.au_id,au_lname,au_fname,phone,address,city,state,zip,contract,__ta.title_id,au_ord,royaltyper,title,type,pub_id,price,advance,royalty,ytd_sales,notes,pubdate__from pubs..Authors A __Left Outer join Pubs..Titleauthor TA on A.au_id=Ta.Au_id__Left outer Join Pubs..Titles T on T.Title_Id=TA.Title_ID^)____Quotes used on the inside have to be doubled up so they dont interfere with string sysntax____Letting th server sort out the syntax and not R&R____Very complex sql can be done with procedures and or dynamic sql__ creating/updating temp tables on the fly and using them in the report live. (needs test on creation time (Field) to stop data re-generating every preview ... ie regen if more than 3 mins old).______I hope this is of help____Andrew Bessey__UK____