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

Expand|Select|Wrap|Line Numbers

 
 if((field IS NULL), 'XX', field) 

 ISNULL(field),'XX',field 

 CASE WHEN field IS NULL then 'XX' else field end 

 IF(field IS NULL,'XX',field)

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

Expand|Select|Wrap|Line Numbers

  
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

Expand|Select|Wrap|Line Numbers

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

Quote:

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,

Expand|Select|Wrap|Line Numbers

  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

MYSQL 'NULL' cant be replased

re: MYSQL 'NULL' cant be replased

re: MYSQL 'NULL' cant be replased

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about