Using formula with SUM(column) with possibly no records

biguniverse · **Joined:** Fri Jul 29, 2005 2:11 pm **Posts:** 21

Hello. I have the following mapping but I'm getting an error possibly due to the fact that "amountAllocated" is null as a result of no rows.

Code:

<class name="AccountAllocation" table="ACCOUNT_ALLOCATION">>
   <id name="accountAllocationId" column="ACCT_ALCN_ID">
      <generator class="assigned"/>
   </id>       
   <property name="accountId" column="ACCT_ID" />
   <property name="amountAllocated"
           formula="(SELECT SUM(a.ALCN_AMT) FROM ALLOCATION a WHERE a.ACCT_ALCN_ID = ACCT_ALCN_ID)"/>

How do I get around this? There is a possibility that there might not be any "amountAllocated" (rows) in which case I'd like this set to 0 (zero). Also, is it possible that my database (Oracle 9i) might not support this?

jdl · **Joined:** Thu May 26, 2005 2:08 pm **Posts:** 99

Can you just add "AND a.ALCN_AMT IS NOT NULL" to your WHERE clause?

Or try this sum instead:

SUM(IF(a.ALCN_AMT IS NULL, 0, a.ALCN_AMT))

Note: That's what it would look like for MySQL. I don't know if those are appropriate for Oracle, but I'd have to believe that they have an equivilent set of methods.

sven · **Posted:** Tue Aug 16, 2005 12:39 pm

In Oracle you would use the following in order to replace a NULL value with a certain other value:

Code:

NVL(column, 0)

Since NVL is Oracle-specific you can try the ANSI-SQL expression COALESCE in Hibernate:

Code:

SELECT SUM(COALESCE(a.ALCN_AMT, 0)) FROM ALLOCATION a WHERE a.ACCT_ALCN_ID = ACCT_ALCN_ID

See chapter 15.8 of the documentation.

Best regards
Sven

sven · **Posted:** Tue Aug 16, 2005 12:41 pm

Well, you can also use NVL - it's native SQL... :)

Best regards
Sven

biguniverse · **Joined:** Fri Jul 29, 2005 2:11 pm **Posts:** 21

Both of these worked! Thanks a bunch.

Code:

SELECT COALESCE(SUM(a.ALCN_AMT), 0)
SELECT NVL(SUM(a.ALCN_AMT), 0)