Change null by othe value

[imeza]

Hi guys. I'm working in IReport 3.0.0 I have some subreports inside a main report and I need to show a value (as zero for ex) if the subreport results is null, or if the value of the return variables of the subreport is null. How can I do that??

In the same way I think that I can solve the problem returning a value (zero for ex) from the queryif there's no rows as query results. I try using the following code, but it does't work.

Any idea will be good received

Code:

SELECT     NVL(COUNT(DISTINCT(SAI.ID_SAI)),0),     PERSONA.NOMBRE1,     PERSONA.APELLIDO1,     PERSONA.APELLIDO2 FROM     ...WHERE   ...GROUP BY      PERSONA.NOMBRE1,     PERSONA.APELLIDO1,     PERSONA.APELLIDO2

[walmillard]

You could add some code in the ${Field} to handle the case where a null results... like this:

 

$F{Foo} == null?0:$F{Foo}

 

This will print the value in Foo or 0 (zero). Note Foo is an Integer Type, for a String type you would need "0".

If you have a whole SQL Query that returns no data you can use the "No-Data Section" which is a band. From the Report Properties menu

 

[imeza]

I tried using both of the solutions you gave me but didn't work. I think Ireport evaluates the query and if it doesn't return data it never evaluates de field $F{...}

[mdahlman]

imeza,

 

These posts are mixing a little bit the idea of returning a field with the value NULL and the idea of not returning any rows. Oracle NVL syntax (the COALESCE command in standard SQL) is useful to convert a NULL value to something else. But if the query returns no rows, then there is nothing to convert. Likewise, walmillard's suggestion applies only when the field has a value (even if that value is null).

 

But variables have an initial value. So if you have a variable that counts the instances of SAI.ID_SAI, that variable should have the value of 0 that you want.

 

Regards,
Matt