By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

'NULL' value can not replaced in sub query

prabirchoudhury
100+
P: 162
Expand|Select|Wrap|Line Numbers
  1. CRITERIA;
  2. +-------------+--------------+------+-----+---------+-------+
  3. | Field       | Type         | Null | Key | Default | Extra |
  4. +-------------+--------------+------+-----+---------+-------+
  5. | CritCode    | int(4)       | NO   | PRI | 0       |       |
  6. | Description | varchar(150) | YES  |     | NULL    |       |
  7. | CritGroup   | varchar(10)  | YES  |     | NULL    |       |
  8. | Detail      | varchar(30)  | YES  |     | NULL    |       |
  9. +-------------+--------------+------+-----+---------+-------+
  10.  
  11.  
  12. SCHCRIT;
  13. +------------+--------+------+-----+---------+-------+
  14. | Field      | Type   | Null | Key | Default | Extra |
  15. +------------+--------+------+-----+---------+-------+
  16. | SchemeCode | int(6) | NO   | PRI | 0       |       |
  17. | CritCode   | int(4) | NO   | PRI | 0       |       |
  18. +------------+--------+------+-----+---------+-------+
  19.  
  20. SCHEMES;
  21. +------------------------+---------------+------+-----+---------+-------+
  22. | Field                  | Type          | Null | Key | Default | Extra |
  23. +------------------------+---------------+------+-----+---------+-------+
  24. | SchemeCode             | int(6)        | NO   | PRI | 0       |       |
  25. | SourceOrg              | int(6)        | YES  |     | NULL    |       |
  26. | Title                  | varchar(100)  | NO   | MUL |         |       |
  27. | Objectives             | mediumtext    | YES  |     | NULL    |       |
  28. | UpdateDate             | date          | YES  |     | NULL    |       |
  29. | CheckDates             | char(1)       | YES  |     | NULL    |       |
  30. | DecisionMakers         | mediumtext    | YES  |     | NULL    |       |
  31. | DecisionTime           | mediumtext    | YES  |     | NULL    |       |
  32. | GroupIndividuals       | char(1)       | YES  |     | NULL    |       |
  33. | EligibleDistricts      | mediumtext    | YES  |     | NULL    |       |
  34. | Eligibility            | mediumtext    | YES  |     | NULL    |       |
  35. | organisationID         | int(11)       | YES  |     | NULL    |       |
  36. +------------------------+---------------+------+-----+---------+-------+
  37.  
  38. Main query: 
  39. select DISTINCT d.title, d.schemecode, d.objectives, 
  40.        d.eligibility, d.eligibledistricts
  41.  
  42.       ,( select DISTINCT COUNT(cr.CritCode) AS Rank 
  43.          from CRITERIA cr 
  44.          INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode 
  45.         where sc.SchemeCode= a.schemecode 
  46.        AND cr.CritCode in(71,5043, 5074, 5024, 5025, 4003, 0)) AS match_rank 
  47.       ,(select IF(cr.Description) IS NULL,'XX',cr.Description)
  48.         from CRITERIA cr 
  49.         INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode 
  50.         AND cr.CritCode = 400 ) AS cost1  
  51.  
  52.  FROM SCHCRIT a inner join SCHCRIT b on(a.schemecode=b.schemecode) 
  53.        inner join SCHCRIT c on(a.schemecode=c.schemecode) 
  54.        inner join SCHEMES d on(a.schemecode=d.schemecode) e) 
  55.        inner join personOrganisation po on (d.sourceorg=po.personid)) 
  56.  WHERE (a.critcode =1 or a.critcode =71)
  57.        and c.critcode in (4000, 4003, 0, 0, 0) 
  58.        and b.critcode in (5000, 5043, 5074, 5024, 5025) 
  59.        order by match_rank DESC, cost1 ASC
  60.  
  61. subquery: this the part of main query mention above and here is having problem with
  62. select IF(cr.Description) IS NULL,'XX',cr.Description)
  63.         from CRITERIA cr 
  64.         INNER JOIN SCHCRIT sc on sc.CritCode=cr.CritCode where sc.SchemeCode= a.schemecode 
  65.         AND cr.CritCode = 400 ) AS cost1
Activity:

I have three tables to pull data from when user is making a search with different 'CRITERIA' those are attached with
different 'SCHEMES'. now i am dysplayning searched results with different sort order.
1. Order by maximum CRITERIA matched SCHEMES ( that i am getting from the subquery result field "AS match_rank" )
2. then 2nd sort is on "cost1" that sorting among given "match_rank". cost1 is coming with some null values those are coming in the top
when i do the sorting in present.
3. both sorting are associated with my main query.


Problem: my main query working fine and fetching all expecting data, but in the second sort on cost1 (that i mentioned above)
"null" value fields are coming onthe top but i wanted them in the bottom. then i tried with different functions to replace NULL value
to bring those end of the sesond sorting but null cant being replaced.

i tryed those differet way with subquery
Expand|Select|Wrap|Line Numbers
  1. 1. if((field IS NULL), 'XX', field) 
  2. 2. ISNULL(field),'XX',field 
  3. 3. CASE WHEN field IS NULL then 'XX' else field end 
  4. 4. order by case when isNULL(cr.Description) then 1 else 0 end, cr.Description asc 
  5. 4. IF(field IS NULL,'XX',field) 
but none of those replacing NULL value

please help me on that.

Note:
1. main query and sub query working fine and giving expected result
2. ISNULL(), IF(), CASE statement working if i use them separately and able to replace NULL but
not as a subquery

thanks in advance
Jul 29 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.