Topic: IfNull is what I need?

riogrande    -- 02-03-2008 @ 3:28 PM
  When the country is blank I want the sql to set the value as a string: "Not defined" I am getting an error here:

I get an error however:
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(country=','Not Defined', country) where month(visit) = 2 GROUP BY co' at line 3:


<cfquery name="q_countries" datasource="daadmindb">
SELECT UCASE(country) AS Country, COUNT(*) AS country_count, Visit
FROM clientinfo
IF(country=','Not Defined', country)
where month(visit) = #url.monthnowis#
GROUP BY country
HAVING country_count > 5
ORDER BY country_count

This message was edited by riogrande on 2-4-08 @ 8:35 AM

Lossed    -- 02-10-2008 @ 7:55 PM
  Hey m8,

So, you allow a null default value for the country field in the DB?

If so, you could do one of two things:

1. update the table to change the default value to 'Not Defined', or:
ifNull(country,'Not Defined')

BTW, <cfqueryparam  Smile


When the only tool you have is a hammer, everything looks like a nail Smile

This message was edited by Lossed on 2-10-08 @ 8:10 PM

riogrande    -- 02-26-2008 @ 3:55 PM
  I don´t get an error but the ifnull doesn´t work.
If countryid is empty null then 'Not defined'. I will try and add it to a default in my db but how do I sort out the ifnull sql? TIA

<cfquery name="countryname" datasource="miner">
SELECT clientinfo.countryid,, COUNT(*) AS country_count,  ifNull(clientinfo.countryid,'Not Defined')  
FROM clientinfo
JOIN countries ON clientinfo.countryid = countries.countryid
WHERE month(visit) = <cfqueryparam value = "#url.monthnowis#" CFSQLType = "cf_sql_integer"   maxLength = "2" null = "No" list = "No">
<cfif url.daynowis neq "">
AND day(visit) = <cfqueryparam value = "#url.daynowis#"      CFSQLType = "cf_sql_integer"      maxLength = "2" null = "No" list = "No">
GROUP BY countryid  
HAVING country_count < #newvalue#
ORDER BY country_count

cfSearchin    -- 02-26-2008 @ 4:53 PM

I don´t get an error but the ifnull doesn´t work.

Then that suggests the values are not really NULL.  The function will not work on other values like say, an empty string "".  If they are not really null, use CASE statement.

This message was edited by cfSearchin on 2-26-08 @ 4:55 PM

riogrande    -- 02-26-2008 @ 10:58 PM
  Thanks for pointing that out it is a strange concept nothing is different from null! I changed the db default and working on a case solution

Lossed    -- 02-27-2008 @ 4:16 AM
  To help un-strange the use of the null marker, perhaps this may help.

Often we use the term "null value" and that probably confuses matters because null IS NOT A VALUE. It is a marker that identifies that there is no value. An empty string is a value - it a string with nothing in it.

It may be worth asking yourself whether there is any merit to allowing both empty strings and null markers.  In some cases there are. If you persist with such an arrangement for your table, then you could use case or  you can use the fact that null is not a value to your advantage with the if() conditional function.

Recall that if(a,b,c) evaluates a, returning b if a is true, else it returns c. So you could use:

'Not Defined') as country

If country is null then country<>'' will never be true because the comparison cannot be made in this context because null is not a value so can't be compared with an empty string.

When the only tool you have is a hammer, everything looks like a nail Smile

This message was edited by Lossed on 2-27-08 @ 4:17 AM

riogrande    -- 02-28-2008 @ 9:23 AM
  That was interesting for me thanks for the link.

So what do you do Lossed with regards to this issue in real life with mysql- not use nulls at all? Do you use default values in fields? Interested to know what the pros do so I can do that Smile  Smile

This message was edited by riogrande on 2-28-08 @ 11:53 AM

Lossed    -- 02-28-2008 @ 3:32 PM
  Very generally, I tend to allow both into the DB, because each portrays slightly different information. One is saying no value has been given, the other is saying the value given was empty. Each has there own use in different contexts. But if you don't need such a distinction, then you could never allow nulls and default to empty strings, or allow nulls and never allow empty strings into the DB.

But please do not consider me a pro. I'm far from it. When I get the chance to natter with a DBA I'll ask this question and post back in here their answer. It could take a while so please don't hold your breath.

When the only tool you have is a hammer, everything looks like a nail Smile

riogrande    -- 02-29-2008 @ 10:51 AM
  Thanks for clearing that up. But is it normal to have default values in dbs fields?

EasyCFM.COM ColdFusion Forums :