How To Ignore Null Value (Case + Group By)

[red_ruewei]

Hi,

I'm sorry if I had post duplicate post.

I had join this 2 table (dt_dict_tbl - store dictionary, sdn5_5 - store accumulate value for reporting).

Here the table structure

dt_dict_tbl

CREATE TABLE `dt_dict_tbl` (
    `dtDictCode` VARCHAR (30),
    `dtDictDesc` VARCHAR (600),
    `rptCode` VARCHAR (30),
    `rptSubCode` VARCHAR (30),
    `dtCreatedDate` TIMESTAMP ,
    `dtColumnName` VARCHAR (150),
    `sort` DOUBLE 
); 

sdn5_5

CREATE TABLE `sdn5_5` (
    `idTbl` DOUBLE ,
    `year` DOUBLE ,
    `program` VARCHAR (30),
    `typeQuantity` VARCHAR (30),
    `Quantity` VARCHAR (300),
    `updateDate` TIMESTAMP 
);

For column program and typeQuantity, it will read info from dt_dict_tbl;

So for reporting i had try below query (i just try for one program first which is D312)

SELECT dtDictDesc,program,typeQuantity,
CASE WHEN typeQuantity='D460' THEN Quantiti  END AS dateStart,
CASE WHEN typeQuantity='D461' THEN Quantiti  END AS dateEnd,
CASE WHEN typeQuantity='D462' THEN Quantiti  END AS Location,
CASE WHEN typeQuantity='D334' THEN Quantiti  END AS Quantity1,
CASE WHEN typeQuantity='D335' THEN Quantiti  END AS Quantity1,
CASE WHEN typeQuantity='D336' THEN Quantiti  END AS Quantity1,
CASE WHEN typeQuantity='D337' THEN Quantiti  END AS Quantity1,
CASE WHEN typeQuantity='D338' THEN Quantiti  END AS Quantity1
FROM sdn5_5 p INNER JOIN dt_dict_tbl d ON (d.dtDictCode= p.program) WHERE program='D312'  ;

and here (attachment) the result that i got.

I aspect the result will show in one record
like this
Promotion Program A | 12/12/2013 | 26/12/2013 | sunshine resort | 83541.00 | 13 | 48 | 13 | 24

How can i ignore this null value?

thank you soo much to take a look for this issue. i really appreciate your time and attention.

Tq :)

[ravikash123]

Hi red_ruewei,

you may use "ISNULL" keyword in the select case statement and set a default value in it.

Like:

SELECT dtDictDesc,program,typeQuantity,
    CASE WHEN typeQuantity='D460' THEN ISNULL(Quantiti,0) END AS dateStart,
        CASE WHEN typeQuantity='D461' THEN ISNULL(Quantiti,0) END AS dateEnd,
        CASE WHEN typeQuantity='D462' THEN ISNULL(Quantiti,0) END AS Location,
        CASE WHEN typeQuantity='D334' THEN ISNULL(Quantiti,0) END AS Quantity1,
        CASE WHEN typeQuantity='D335' THEN ISNULL(Quantiti,0) END AS Quantity1,
        CASE WHEN typeQuantity='D336' THEN ISNULL(Quantiti,0) END AS Quantity1,
        CASE WHEN typeQuantity='D337' THEN ISNULL(Quantiti,0) END AS Quantity1,
        CASE WHEN typeQuantity='D338' THEN ISNULL(Quantiti,0) END AS Quantity1
        FROM sdn5_5 p INNER JOIN dt_dict_tbl d ON (d.dtDictCode= p.program) WHERE program='D312' ;

[red_ruewei]

thanks ravikash123. really appreciate it. i had try to run this but i got this error instead "Incorrect parameter count in the call to native function 'ISNULL' ". i try to check about it this first.

thanks again for your reply. u give me a hope :)