MYSQL 'NULL' cant be replased

[prabirchoudhury]

hey all i am having problem with NULL values to replace with, i have been using all the way to replace NULL value but non of those working for me. NOT NULL field is replacing with abut not the NULL field. i have tested the conditions to replace NULL in other simple query those are working but not the code bellow.
i have tryed with

1. if((field IS NULL), 'XX', field) 
2. ISNULL(field),'XX',field 
3. CASE WHEN field IS NULL then 'XX' else field end 
4. IF(field IS NULL,'XX',field) 
 
 

my origunal code down here, please any one could help.

 
select DISTINCT d.title, d.schemecode, d.objectives, 
d.eligibility, d.eligibledistricts, 
date_format(f.closingdate, '%d/%m/%Y') as closedate, o.type 
,( select DISTINCT COUNT(cr.CritCode) AS Rank 
from CRITERIA cr 
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode 
where sc.SchemeCode= a.schemecode 
AND cr.CritCode in(71,5043, 5074, 5024, 5025, 4003, 0)) AS match_rank 
,(select IF(Description IS NULL,'XX',Description) 
from CRITERIA cr 
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode 
AND cr.CritCode = 4003) AS cost1 
 
FROM SCHCRIT a inner join SCHCRIT b on(a.schemecode=b.schemecode) 
inner join SCHCRIT c on(a.schemecode=c.schemecode) 
inner join SCHEMES d on(a.schemecode=d.schemecode) 
inner join PROTECTS e on(d.protectlevel=e.protectlevel) 
inner join SCHORD f on(a.schemecode=f.schemecode) 
inner join personOrganisation po on (d.sourceorg=po.personid) 
inner join organisation o on(po.organisationid=o.organisationid) 
WHERE (a.critcode =1 or a.critcode =71) 
and c.critcode in (4000, 4003, 0, 0, 0) 
and b.critcode in (5000, 5043, 5074, 5024, 5025) 
and e.searchresults = 'T' order by match_rank DESC, cost1 ASC
 
 

thanks in advanced
:)

[SLauren]

You can try with

IFNULL(<field_name>,Value)

If the field is null then it will replace it with the given value.

Thanks,
Lauren

[prabirchoudhury]

thanks for your reply...
the problem is not with the

IFNULL(<field_name>,Value)

IFNULL() or IF() function to replace the NULL value but when i add this function with subquery then it wont work, my main query is working fine itself and when i added the subquery that work fine , but i wanted to sort on this subquery return but, subquery fetches with null values those comes on top with " ORDER BY ASC" sort, so wanted to put all null values on the end os the sorting .. when i am trying to replace NULL using those functions then it doesn’t replace NULL,

 This my sub query
,(select IF(Description IS NULL,'XX',Description)  
from CRITERIA cr  
INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode  
AND cr.CritCode = 4003) AS cost1  
 

thanks, prabir