From dec86d60d37f2e90063f0a20eb61be8de4f7d32c Mon Sep 17 00:00:00 2001 From: Lam Voong Date: Wed, 20 Jul 2016 16:08:19 -0700 Subject: [PATCH] CSPACE-6948 updated query to display message when no objects are related. --- .../src/main/resources/coreLoanIn.jrxml | 15 ++++----------- 1 file changed, 4 insertions(+), 11 deletions(-) diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/coreLoanIn.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/coreLoanIn.jrxml index 188836bee..220b14ae4 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/coreLoanIn.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/coreLoanIn.jrxml @@ -32,33 +32,26 @@ end AS site, ong.objectName AS "objectname", case when (bd.item is not null and bd.item <> '') then regexp_replace(bd.item, '^.*\)''(.*)''$', '\1') + when co.objectnumber is null then 'No objects are related to this Loan In.' else '' end AS description, case when (oppp.objectproductionpeople is not null and oppp.objectproductionpeople <> '') then regexp_replace(oppp.objectproductionpeople, '^.*\)''(.*)''$', '\1') else '' end as productionpeople - -FROM loansin_common lic -JOIN hierarchy h1 ON (lic.id=h1.id) +FROM hierarchy h1 +JOIN loansin_common lic ON (lic.id=h1.id) LEFT OUTER JOIN relations_common rc1 ON (h1.name=rc1.subjectcsid) LEFT OUTER JOIN hierarchy h2 ON (rc1.objectcsid=h2.name) LEFT OUTER JOIN collectionobjects_common co ON (h2.id=co.id) - LEFT OUTER JOIN hierarchy h3 ON (co.id = h3.parentid AND h3.primarytype='objectNameGroup' AND h3.pos=0) LEFT OUTER JOIN objectnamegroup ong ON (ong.id=h3.id) - LEFT OUTER JOIN collectionobjects_common_briefdescriptions bd ON (bd.id=co.id and bd.pos=0) - left outer join hierarchy h4 on (co.id=h4.parentid and h4.pos=0 and h4.name='collectionobjects_common:objectProductionPeopleGroupList') left outer join objectproductionpeoplegroup oppp on (oppp.id=h4.id) - - left outer join hierarchy hlgl on (lic.id=hlgl.parentid and hlgl.pos=0 and hlgl.name='loansin_common:lenderGroupList') left outer join lendergroup lg on (lg.id=hlgl.id) - -WHERE rc1.subjectcsid = $P{csid} - +WHERE h1.name = $P{csid} ORDER BY objectnumber]]> -- 2.47.3