MySQL – Using SELECT Statement with IFNULL Function

MySQL

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.

Best Answer

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).

Related Question