prevent NULL in mysql view, de-normalization

Question

Dear all,

I´d love to do something like this, but with a working syntax of course :)

MIN(CASE WHEN col=2 
THEN CASE WHEN answer IS NULL THEN 0 ELSE answer END END)

i am trying to prevent a view from getting NULL values and use zeros instead. Here is my SELECT query that creates the view:

SELECT result,question_id,
MIN(CASE WHEN col=1
THEN answer
END) AS col1,
MIN(CASE WHEN col=2 
THEN answer
END) AS col2,
MIN(CASE WHEN col=3
THEN answer
END) AS col3
FROM answers 
GROUP by result,question_id

Basically I get a nice result that looks like this

result   question_id   col1   col2   col3
1            2          10     20      70
2            2          80     20     NULL
3            3          0     100       0

I do not have any performance problems and the original table is updated regularly that´s why I'd love to stay with the view as opposed to a sp. The table that you see disperse a relational structure of a survey that surveys probabilities. That the cols of every row have to add up to 100. If someone is really sure that something e.g. col3 wont happen he can either fill in 0 to the other form fields or leave the field blank. The form validator checks only if it all adds up to 100. If the field is left blank, the relational there is NO entry in the relational table, so the dispersed view writes a NULL.

I´d love to make it write a "0" but i do not know how! Thx for any suggestions in advance!

Answer 1

You're looking for IFNULL(..something...,0)

---

Apparently have to spell it out:

IFNULL(MIN(CASE WHEN col=3 THEN answer END),0)

Answer 2

SELECT name AS Name, category AS Category, IF(winter>500, "Sells", "Slow") AS Trend FROM sales;

That was from http://www.java2s.com/Code/SQL/Flow-Control/UseIFinselectclause.htm