0

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.

  • Print the value of initial_stock after setting it. – Abdul Manaf Feb 8 '17 at 9:54
  • @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 – Afrooz Feb 8 '17 at 10:19
  • Yes , you can add a statement SELECT initial_stock ; after this query and once you call the SP it would print the data. – Abdul Manaf Feb 8 '17 at 11:31
0

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

  • Dear Rick, Thank you for your response. I edited my question and added the simplified version at the end. – Afrooz Feb 9 '17 at 6:24
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.