The only way to force a numeric zero instead of a NULL is to alter the table. Don't allow NULL's in the field and set a default
value of 0. However, you then lose the ability to determine is a value was actually entered or if 0 was entered. Don't know if that
matter to you.
Otherwise, you can use the IFNULL function (can't be helped). For example:
SELECT IFNULL(0,3); <-- Returns 0
SELECT IFNULL(NULL,3) <-- Returns 3
SELECT (IFNULL(t3.m,0) * 1000000) ...
The IFNULL function shouldn't slow down your query noticeably, especially since
you are already using a HAVING filter.
----- Original Message -----
From: "Asif Lodhi" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, August 10, 2006 6:14 AM
Subject: Get a Numeric Zero instead of NULL in SELECT
Hi,
I have a query:
insert into tmp2 (x)
select ((t3.m * 1000000)+b.id ) as x2
from tmp3 t3 LEFT JOIN
(select ((max(x)) - ((max(x) div 1000000) * 1000000))+1 as pid
from tmp2
where ((x div 1000000)=2147)
having (((max(x)) - ((max(x) div 1000000) * 1000000)) < 483648)) b
ON t3.m=b.pid
The table t3 has a single INT field "m" with a single record and
value: 2147. The table tmp2 has a single INT field "x" with no
records. The above code returns NULL in the 2nd column of the SELECT
that I can't add to or multiply with another number to get the final
value to insert into the table tmp2. I am using INNODB tables on
5.0.22 running on a WINDOWS-XP-SP2. Around 75 Clients connect from
VB6/Windows. I am STARTing TRANSACTIONs and COMMITing them from VB6
client-code. Since I am also using SQL STRICT mode with more stricter
parameters in the MY.INI.
I don't want to use functions as that will impair the query speed.
Do you know of any way that I could use to get a numeric ZERO instead
of a null in the 2ND column of the SELECT?
Any suggestions/comments?
Thanks in advance,
--
Asif
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
