How will i add fields returned from SQL Query

[2006 IR Open Dicussion]

By: Neil H - neilhay

How will i add fields returned from SQL Query

2002-12-06 17:55

I am trying to add fields returned back from SQL query and display the sum of it. My query look like this:

 

Select A, B, C from Table

 

I want to have: SumABC= A+B+C

 

how will i do that?

 

 

 

 

By: Teodor Danciu - teodord

RE: How will i add fields returned from SQL Query

2002-12-07 00:35

 

Hi,

 

Lets say you have already defined the 3 fields which

are all integers. If you did not, declare them in you report.

 

You can add them like this:

 

new Integer(${A}.intValue() + ${B}.intValue() + ${C}.intValue())

 

Remember: all report expressions are in fact real Java expressions.

 

I hope this helps.

Teodor

 

 

 

 

 

By: Neil H - neilhay

RE: How will i add fields returned from SQL Query

2002-12-09 07:23

Great thanks. this works.. but whatif the Integer(${A}.intValue() = null? can i setup default value of 0?

 

I am getting 0 as sum if one of the column is "null"?

 

thanks,

neil

 

 

 

 

By: Chuck Deal - cdeal

RE: How will i add fields returned from SQL Query

2002-12-09 07:49

If your database supports it, you could check for null in the query.

 

select ISNULL(A, 0) from table

 

 

 

 

By: Neil H - neilhay

RE: How will i add fields returned from SQL Query

2002-12-09 08:10

Thanks for help!!!

 

I am using Db2 and in DB2 you can using COALESCE(b,0)

 

Neil