Hi,
I am replying to my own post to tell you that now I am using
CASE WHEN {condition} THEN .... END
construct to check for NULL and returning numeric ZERO. On the face
of it, CASE doesn't
seem to be function - it's an operator - isn't?
However, I would now like to ask you whether I can use some kind of an
Oracle-like USE_INDEX optimization hint in MySQL to get it to use a
specific index in the SELECT? I used explain on it and it tells me
that it's using index on all except on the the query I am using as the
2nd table. Is there any way I can speed it up?
Any suggestions?
--
Asif
On 8/10/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:
I have a query:
insert into tmp2 (x)
select ((t3.m * 1000000)+b.id ) as x2 /* I AM USING CASE WHEN cond .. here */
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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
