Hi Forum, I've been struggling today to try and get an entity-view working with conditional joins using release4.0. I have given up and reverted to using a plain SQL view. Should I be able to do the following SQL as a view-entity?
Many thanks in advance... Chris create view vClearanceReport as SELECT insr.strIsrCompany, insr.strIsrBranch, insr.strIsrCode, upl.ingUplNumber, upl.strUplClaimNum, upl.strUplRegistration, upl.strLocation, upl.strUplCategory, uplChg.ingChgRealisation, veh.dtmVehYear, veh.strVehVIN, vari.strMake, vari.strModel, DateDiff(dd,upl.dtmUplPickupDate,getdate()) days, case when fpay.ingUpLift is not null then 'YES' else 'NO' end as Cleared, case when fhld.ingUpLift is not null then 'YES' else 'NO' end as InsuranceHold, imgs.totalNumOfImages, imgs.numOfImagesCopiedToSSRS FROM tblUplift AS upl JOIN tblUpliftFlags AS flg ON ( upl.ingUplNumber=flg.ingUplift and flg.strFlag='Pay' and flg.blnAgreed=0 ) JOIN tblVehicle AS veh ON (upl.ingUplNumber=veh.ingUpliftNumber) JOIN tblInsurer AS insr ON (upl.strUplInsurer=insr.strIsrCode) JOIN tblVariant AS vari ON (veh.ingVehicle=vari.ID) JOIN tblUpliftCharges AS uplChg ON (upl.ingUplNumber=uplChg.ingChgUplift) LEFT JOIN ( select ingUpliftNumber, max(ingImgNum)+1 as totalNumOfImages, count(isCopiedToReportServer) as numOfImagesCopiedToSSRS from tblVehicleImagesProcessLink group by ingUpliftNumber ) AS imgs ON (upl.ingUplNumber=imgs.ingUpliftNumber) left join tblUpliftFlags fpay on (upl.ingUplNumber = fpay.ingUplift and fpay.strFlag = 'Pay' and fpay.blnAgreed = 1) left join tblUpliftFlags fhld on (upl.ingUplNumber = fhld.ingUplift and fhld.strFlag = 'InsuranceHold' and fhld.blnAgreed = 1) WHERE upl.dtmUplPickupDate Is Not Null And upl.strUplStatus In ('Awaiting Uplift','Uplifted') -- View this message in context: http://www.nabble.com/entity-view-conditional-joins-tp23162926p23162926.html Sent from the OFBiz - User mailing list archive at Nabble.com.