I have a text field on my report that will not always have data. When this field is blank (NULL) the report will not generate. I want to specify that if the field in the database is NULL that it should report as "N/A", thus allowing the report to generate.
The field in question is "dbo.actionplan.actionplanid" and is being called from a joined table in the database per the following query:
SELECT dbo.incident.incidentid,
dbo.incident.incidentdesc,
dbo.incident.incidenttype,
dbo.incident.incidentstatus,
dbo.incident.incidentdt,
dbo.incident.reportedby,
dbo.incident.rootcause,
dbo.incident.triage,
dbo.incident.departmentid,
dbo.incidentfind.incidentfinddesc,
dbo.incidentfind.investigationlog,
dbo.incidentfind.u_response,
dbo.incidentfind.u_findinggroup,
(select refdisplayvalue from refvalue where reftypeid= 'Findinggroup' and refvalueid=incidentfind.u_findinggroup) reftypedisplay,
dbo.incident.explanation,
dbo.incident.standardreference,
dbo.incident.riskdone,
dbo.incident.risknotdone,
dbo.incident.locationid,
dbo.LV_Query_GetLocPathLabel(incident.locationid,' / ',null) as LocPathLabel,
dbo.incident.expirationdt,
dbo.incident.u_timepoint,
dbo.incident.incidentauditby,
dbo.incident.incidentauditdt,
dbo.incident.incidentauditreference,
dbo.incident.severity,
dbo.incident.notes,
dbo.actionplan.actionplanid
FROM dbo.incidentfind
INNER JOIN dbo.incident ON
dbo.incident.incidentid = dbo.incidentfind.incidentid
INNER JOIN dbo.actionplan ON
dbo.actionplan.incidentid = dbo.incident.incidentid
WHERE
dbo.incident.incidentid = $P{incidentid}
1 Answer:
If "dbo.actionplan.actionplanid" is allowed to be null then you must change the join condition on "dbo.actionplan" to be an OUTER JOIN. It is the INNER JOIN to that table that is causing nothing to be returned when there is no "dbo.actionplan.actionplanid".
SELECT dbo.incident.incidentid , dbo.incident.incidentdesc , dbo.incident.incidenttype , dbo.incident.incidentstatus , dbo.incident.incidentdt , dbo.incident.reportedby , dbo.incident.rootcause , dbo.incident.triage , dbo.incident.departmentid , dbo.incidentfind.incidentfinddesc , dbo.incidentfind.investigationlog , dbo.incidentfind.u_response , dbo.incidentfind.u_findinggroup , (SELECT refdisplayvalue FROM refvalue WHERE reftypeid= 'Findinggroup' AND refvalueid=incidentfind.u_findinggroup) AS reftypedisplay , dbo.incident.explanation , dbo.incident.standardreference , dbo.incident.riskdone , dbo.incident.risknotdone , dbo.incident.locationid , dbo.LV_Query_GetLocPathLabel(incident.locationid,' / ',NULL) AS LocPathLabel , dbo.incident.expirationdt , dbo.incident.u_timepoint , dbo.incident.incidentauditby , dbo.incident.incidentauditdt , dbo.incident.incidentauditreference , dbo.incident.severity , dbo.incident.notes , dbo.actionplan.actionplanid FROM dbo.incidentfind INNER JOIN dbo.incident ON dbo.incident.incidentid = dbo.incidentfind.incidentid LEFT OUTER JOIN dbo.actionplan ON dbo.actionplan.incidentid = dbo.incident.incidentid WHERE dbo.incident.incidentid = $P{incidentid}
If you still would like for actionplanid to return "NA" when null then I suggest looking up the ANSI SQL standard for COALESCE().
Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.