Cassandra - 1 year ago 46

SQL Question

I have a pretty large mysql call which I won't bother showing it all as it's not really important. The part that IS working fine before requiring modification looked like this:

`COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq, d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',`

This would return all the

`apFunReq`

Now it's been decided we need another tables data to join the process, however the required data does not sit all in one column on this new table, instead it needs to be added up collectively over 5 columns. So I have implemented this:

`COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq, (IFNULL(d10.reqFunding_01, 0) + IFNULL(d10.reqFunding_02, 0) + IFNULL(d10.reqFunding_03, 0) + IFNULL(d10.reqFunding_04, 0) + IFNULL(d10.reqFunding_05, 0)), d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',`

Now this does actually work for the new table rows in the result BUT it breaks a significant portion of what was previously returning ok. Now much of the other tables values are just returning as 0.

Clearly I need to return null values to be 0 for data within d10 for the addition to work, but I want null values from the other tables to remain null and I want the data for all tables to obviously work as desired.

I'm not sure what I am doing wrong in order to get the desired result. Can someone help me on this please?

C

Answer Source

The simple way to solve this is to use `NULLIF`

after you add up all the `IFNULL`

ed values like so:

```
COALESCE(d1.apFunReq, d2.apFunReq, d3.apFunReq, d4.apFunReq, d5.apFunReq, d6.apFunReq, d8.apFunReq,
NULLIF(IFNULL(d10.reqFunding_01, 0) + IFNULL(d10.reqFunding_02, 0) + IFNULL(d10.reqFunding_03, 0) + IFNULL(d10.reqFunding_04, 0) + IFNULL(d10.reqFunding_05, 0), 0),
d2old.apFunReq, d3old.apFunReq, d5old.apFunReq, '') AS 'Funding Required',
```

Obviously, this doesnt work if 0 is a valid answer though.