EasyCFM.COM ColdFusion Forums / MySQL Related Issues / IfNull is what I need?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: IfNull is what I need?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

riogrande
02-03-2008 @ 3:28 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

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:


quote:


<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
</cfquery>



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

Lossed
02-10-2008 @ 7:55 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

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:
2:
ifNull(country,'Not Defined')

BTW, <cfqueryparam  Smile

HTH



Lossed
---------------------------
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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

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, countries.country, 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">
</cfif>
GROUP BY countryid  
HAVING country_count < #newvalue#
ORDER BY country_count
</cfquery>

cfSearchin
02-26-2008 @ 4:53 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

quote:


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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

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:

if(country<>'',
    country,
'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.
    



Lossed
---------------------------
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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

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.



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

riogrande
02-29-2008 @ 10:51 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

Thanks for clearing that up. But is it normal to have default values in dbs fields?


Website Designed and Developed by Pablo Varando.