|
|
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')
|
Locked
