How do I report a text field as NA if it is NULL?

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,
    (select refdisplayvalue from refvalue where reftypeid= 'Findinggroup' and refvalueid=incidentfind.u_findinggroup) reftypedisplay,
      dbo.LV_Query_GetLocPathLabel(incident.locationid,' / ',null) as LocPathLabel,
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 
     dbo.incident.incidentid = $P{incidentid}

Brandy.gaydos's picture
Joined: Feb 22 2021 - 7:23am
Last seen: 3 months 3 weeks ago

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 for direct access to our technical support teams offering guaranteed response times.

arai_4 - 3 months 3 weeks ago

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
     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().

jgust's picture
Joined: Jun 10 2010 - 6:39am
Last seen: 7 hours 24 min ago