troubleshooting Question

MySQL case statement in WHERE clause - condition fails

Avatar of k heitz
k heitz asked on
MySQL Server
11 Comments1 Solution118 ViewsLast Modified:
Hi Experts,
I am having a problem with the CASE condition in the WHERE clause of my SELECT statement. (Sample code below).

The following select is used in a stored procedure which takes in a couple strings. If the string is NULL, the MakeTempTable function for the corresponding table is not invoked, hence the temp table is not created.

I am trying to condition whether or not to use the IN (Select...) in WHERE based on if the string is NULL or not.
However, the Or part of the condition seems to be invoked regardless. I have tried replacing WHEN NULL is NULL with WHEN 'a' = 'a' and the Or is still invoked.

I am new to mySQL so I suspect the structure of condition is wrong and may not be related to CASE statement at all.

Working with Workbench, the following line is throwing the error:
(1=(CASE WHEN NULL is NULL THEN 1 ELSE 0 END) Or pae.pitcherid IN (Select id from temppid100))
Error Code: 1146 Table 'gamedatareport.temppid100' doesn't exist

Here is the sample code. I've hardcoded for the parameters.

/*string for temppid is NULL, so temppid100 is not created*/

/*This line creates tempHid100 used below.*/
Call MakeTempTable ('100','31982,15562,20712',0);


       SELECT  
            pae.PitchAndEventId,
            gh.`Date` AS Date            
       
      FROM    
            GameDataReport.gameheader gh
            INNER JOIN pitchandevent pae ON pae.gameid = gh.gameid
       
      WHERE
            gh.Date >= '2016-08-01' AND
                gh.Date <= '2016-09-18' AND
            (1=(CASE WHEN '31982,15562,20712' IS NULL THEN 1 ELSE 0 END) Or pae.hitterid IN (Select id from temphid100)) AND

                /*This is the line erroring*/
            (1=(CASE WHEN NULL is NULL THEN 1 ELSE 0 END) Or pae.pitcherid IN (Select id from temppid100))

        Order by pae.gameid,pae.recordsequence;
       
call droptemptable ('temppid100');
call droptemptable ('temphid100');
ASKER CERTIFIED SOLUTION
k heitzapplication developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros