can I use select statement for ifnull function?

Question

I need to select a value from a table according to its date. If the record does not exists in the target table, I want to replace it with another value as defualt value instead of Null. Can I do this with ifnull() function?

I have written this piece of code in a procedure, but I only get Null value for this field.

 CREATE PROCEDURE `test`(IN report_date DATE)
 BEGIN
 DECLARE initial_stock INT UNSIGNED;
 SET initial_stock = (select DISTINCT gs_fuel_type.fuel_initial_stock
 from gs_fuel_type 
 WHERE gs_fuel_type.fuel_type_id = 2 );
 drop TEMPORARY table if EXISTS temp_daily_fuel;
 create TEMPORARY TABLE temp_daily_fuel (
 fuel_stock INT UNSIGNED DEFAULT 0 
 );
 insert into temp_daily_fuel (fuel_stock)
 select distinct 
 (select ifnull(gs_fuel_stock.fuel_remainder , initial_stock) 
 from gs_fuel_stock
 where gs_fuel_stock.fuel_type_id = 2
 and gs_fuel_stock.shift_date = date_sub(report_date , INTERVAL 1 day)
 ) as fuel_stock 
 FROM shifts_view
 where shifts_view.shift_date = report_date
 AND shifts_view.fuel_type_id = 2
 GROUP BY shifts_view.fuel_type_id; 
 select * from temp_daily_fuel ;
 END 

Why { select ifnull(gs_fuel_stock.fuel_remainder , initial_stock) .... } still returns null and doesn't replace the initial_stock value for this statement?

and I am quite sure that initial_stock value is not null.

The simplified Version of this code:

 CREATE PROCEDURE `test`(IN report_date DATE)
 BEGIN
 DECLARE i INT UNSIGNED;
 SET i = (select DISTINCT g.i from g WHERE g.id = 2 );
 drop TEMPORARY table if EXISTS t;
 create TEMPORARY TABLE t (
 f INT UNSIGNED DEFAULT 0 
 );
 insert into t (f)
 select distinct 
 (select ifnull(gs.r , i) from gs 
 where gs.id = 2 and gs.date = date_sub(report_date , INTERVAL 1 day) ) as f 
 FROM a
 where a.date = report_date AND a.id = 2
 GROUP BY a.id; 
 select * from t ;
 END 

in this code, I want the value of "i" to be replaced instead of showing NULL.

• Abdul Manaf: Print the value of initial_stock after setting it.
• Afrooz: @AbdulManaf what do you mean? The result of (select DISTINCT gs_fuel_type.fuel_initial_stock from gs_fuel_type WHERE gs_fuel_type.fuel_type_id = 2 ) is 54500 in the gs_fuel_type table
• Abdul Manaf: Yes , you can add a statement SELECT initial_stock ; after this query and once you call the SP it would print the data.

Answer 1

Start with this simplification:

CREATE TEMPORARY TABLE t
INSERT INTO t
 SELECT ... -- Keep this; toss the other parts
SELECT FROM t

Then look around for other simplifications.

Read up on INSERT ... ON DUPLICATE KEY UPDATE ... -- it may be the ultimate simplification.

After all that, I may be able to see what you are asking about (IFNULL).

• Afrooz: Dear Rick, Thank you for your response. I edited my question and added the simplified version at the end.

Answer 2

Thank everyone who tried to help me on this problem, I managed to solve it myself. It turned out that the IFNULL() was functioning correctly, but the problem was that the query result was not null, it simply didn't exist!!!

So, I solved it with 'if not exists' and set the result to Null. after that, the IFNULL() function worked as I expected.

Here's my solution: first of all, I need a new variable "remains":

 DECLARE remains INT;

after that, I used the if structure:

 if not exists 
 (select gs_fuel_stock.fuel_remainder
 from gs_fuel_stock
 where gs_fuel_stock.fuel_type_id = 2
 and gs_fuel_stock.shift_date = date_sub(report_date , INTERVAL 1 day))
then
 set remains = null;
end if;

finally, it's time to use ifnull:

 select ifnull(remains , initial_stock)

and it's done!