Solved

MySQL case statement in WHERE clause - condition fails

Posted on 2016-10-12
11
35 Views
Last Modified: 2016-11-05
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');
0
Comment
Question by:klheitz
  • 6
  • 3
  • 2
11 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41841303
Error Code: 1146 Table 'gamedatareport.temppid100' doesn't exist

can you make sure you got a table: temppid100 in schema: gamedatareport ?

is this question for MySQL or MS SQL ?
0
 

Author Comment

by:klheitz
ID: 41841307
Thanks Ryan.
This is MySQL (I was having issues w/ posting the question & couldn't get MySQL to select).
temppid100 does not exist - so I'm trying to condition around it's use.

Thanks
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41841309
yea, so I have helped to select the correct topic tag now.

temppid100 does not exist - so I'm trying to condition around it's use.
yes, so you need to adjust your query, to either remove that condition, or create the necessary table (which based on your business logic) so that your SQL statement will not hitting any table not exists error.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41841328
Is it possible to provide your entire query?
0
 

Author Comment

by:klheitz
ID: 41842536
Hi Sharath - yes it is below.
Here is the call:
CALL gamedatareport.edgewizard_prod('2015-08-01','2016-09-18',NULL,'31982,15562,20712')

Thank you!
PS: DropTempTable and MakeTempTable functions just either drop or create the temporary tables temppid100 or temphid100.

MYSQL code:
DELIMITER $$
CREATE DEFINER=`iemysql`@`%` PROCEDURE `edgewizard_prod`(IN min_date DATE, IN max_date DATE, IN pIDs VARCHAR(8000), IN hIDs VARCHAR(8000))
BEGIN

call droptemptable ('temppid100');
call droptemptable ('temphid100');

IF pIDs IS not NULL THEN
      Call MakeTempTable ('100',pids,1);
end if;      

IF hIDs IS not NULL then
      Call MakeTempTable ('100',hids,0);
end if;

       SELECT  
            pae.PitchAndEventId,
            gh.`Date` AS Date,
            CONCAT(ifnull(htc.`city or state`,''),' ',ifnull(htc.Nickname,'')) AS `H TeamCurrent`,
            CONCAT(ifnull(ptc.`city or state`,''),' ',ifnull(ptc.Nickname,'')) AS `P TeamCurrent`,
            pae.PitchCount,
            (CASE WHEN pae.PitchInZone = 1 THEN -1 ELSE 0 END) as PitchInZone,
            h.`hitter or pitcher` AS `Hitter or Pitcher`,
            pae.BSCount,
            bs.`early-late` AS `Early-Late`,
            bs.`2strikes` AS `2Strikes`,
            bs.GroupCount,
            bs.`3balls` AS `3Balls`,
            pae.Outs,
            CONCAT(ifnull(p.lastname,''),' ',ifnull(p.firstname,'')) AS Pitcher,
            pae.PitcherSide,
            CONCAT(ifnull(h.lastname,''),' ',ifnull(h.firstname,'')) AS Hitter,
            pae.HitterSide,
            CONCAT(ifnull(c.lastname,''),' ',ifnull(c.firstname,'')) AS Catcher,
            pae.InningNumber,
            spt.PT_Generic AS Pitch_Type,
            spt.PT_Group AS PTypeGroup,
            spt.`FB/NFB`,
            (CASE WHEN pae.pitchspeed = 0 THEN NULL ELSE pae.pitchspeed END) AS Vel,
            r.runners_yn AS `Runners y/n`,
            pae.ROB,
            r.RUNNERS AS `#RUNNERS`,
            r.RISP,
        GetBOP(pae.HitterId,pae.gameid,pae.PitchAndEventId) AS `LineupSpot`,
            e.AB,
            e.H,
            e.`2B`,
            e.`3B`,
            e.HR,
            e.SO,
            e.BB,
            e.PA,
            (CASE WHEN pae.norelatedpitch IS TRUE THEN NULL ELSE 1 END) AS Pitch,
            (CASE WHEN pae.BorS = 'strike' THEN 1 ELSE 0 END) AS Strike,
            act.WH AS `Well-Hit`,
            act.Miss,
            act.InPlay,
            act.SwungOn,
            (CASE WHEN pae.norelatedpitch IS TRUE THEN 0 ELSE act.take END) AS Take,
            (CASE WHEN pae.norelatedpitch IS FALSE AND pae.pitchinzone IS FALSE AND act.`swungon` = 1 THEN 1 ELSE 0 END) AS Chase,
            e.TB,
            sof.SideOfField,
            pae.ScoreDifferential,
            
            loc.LocIMA,
            loc.LocUMD,
            loc.LocExact,
            th.league AS HitterLg,
            tp.league AS PitcherLg,
       
            CONCAT(ifnull(th.`city or state`,''),' ',ifnull(th.nickname,'')) AS HitterTeam,
            CONCAT(ifnull(tp.`city or state`,''),' ',ifnull(tp.nickname,'')) AS PitcherTeam,
            e.SF,
            e.HBP,
            pae.InningHalf,
            pae.PitchNum_ThisAB,
            (CASE WHEN pae.norelatedpitch IS FALSE AND pae.pitchinzone = 0 THEN 1 ELSE 0 END) AS OutOfZn,
            pae.GB,
            pae.FLY,
            spt.EW_Description AS Sub_Type,
            (CASE WHEN pae.medhit IS TRUE THEN 1 ELSE 0 END) AS MediumHit,
            (CASE WHEN sof.`fielddepth` IS NULL THEN NULL ELSE CONCAT(ifnull(sof.`fielddepth`,''),' ',ifnull(sof.`EstimatedDistance (ft)`,''),' ft.') END) AS `Field Depth`,
            TRIM(gh.stadium) AS GameLocation,
            th.division AS `Hitter Division`,
            tp.division AS `Pitcher Division`,
            p.mlbam AS `Pitcher MLBID`,
            h.mlbam AS `Hitter MLBID`,
            gh.HomePlateUmpire,
            pae.hitterdefensiveposition AS `Hitter Pos (in game)`,
            h.position AS `Hitter Roster Pos`,
            p.position AS `Pitcher Roster Pos`,
            (CASE
                  WHEN gh.visitorstartingpitcherid IS NULL THEN 'unknown'
                  WHEN pae.pitcherid IN (gh.homestartingpitcherid, gh.visitorstartingpitcherid) THEN 'SP'
                  ELSE 'RP'
            END) AS `PitcherPos (in game)`,
            (CASE WHEN pae.LeadingOffInning = 1 THEN -1 ELSE 0 END) as LeadingOffInning,
            pae.RBI,
            act.Foul,
            e.XBH,
            (CASE
                  WHEN pae.pitchresult =  '+' AND e.`out` = 1 THEN 1
                  WHEN pae.pitchnum_thisab > 6 AND e.`out` = 1 THEN 1
                  ELSE e.qab
            END) AS QAB,
            (CASE WHEN pae.norelatedpitch IS TRUE THEN 'no pitch' ELSE pae.bors END) AS BallOrStrike,
            (CASE
                  WHEN pae.norelatedpitch IS TRUE THEN 'no pitch'
                  WHEN pae.pitchresult = '' THEN 'Taken'
                  WHEN pae.buntedintoplay IS TRUE THEN 'bunted ball'
                  ELSE pae.pitchresult
            END) AS `Pitch Result`,
            gh.ghuid AS `Game ID`,
            e.IBB,
            e.ROE,
            (CASE WHEN gh.`postseason` = 1 THEN  'Postseason' ELSE  'Regular' END) AS PostSeason,
            (CASE
                  WHEN gh.winningpitcherid = pae.pitcherid THEN 'W'
                  WHEN gh.losingpitcherid = pae.pitcherid THEN 'L'
                  WHEN gh.savepitcherid = pae.pitcherid THEN 'SV'
                  ELSE 'ND'
            END) AS `DEC`,
            (CASE
                  WHEN pae.LineDrive IS TRUE THEN 'Line Drive'
                  WHEN pae.GB = 1 THEN 'Groundball'
                  WHEN pae.GB = 0 THEN 'Flyball'
                  ELSE NULL
            END) AS `BallInPlay Detail`,
            e.`friendlystring` AS Event1,
            pae.`multeventone` AS Event2,
            e.`eventtype` AS `Event Type`,
            (CASE WHEN pae.norelatedpitch IS TRUE THEN 1 ELSE NULL END) AS `Non-Pitch`,
            pae.numoutsonplay / 3 AS IP,
            CASE WHEN pae.numoutsonplay = 0 THEN 0 ELSE 1 END AS `OUT`,
            gh.SeasonHalf,
        GetIP(p.MLBAM) AS `Season IP`,
            pae.WinImpact,
            pae.RunImpact,
            pae.bid AS Dirt,
            pae.GID,
            pae.pa_thisgame AS HitterPA_ThisGame,
            pae.PitcherTimeThruLineup
       
      FROM    
            GameDataReport.gameheader gh
            INNER JOIN pitchandevent pae ON pae.gameid = gh.gameid
            INNER JOIN insideed.`player table` AS h ON h.`player id` = pae.hitterid
            INNER JOIN insideed.`team table` htc ON h.`team id` = htc.`team id`
            INNER JOIN insideed.`player table` AS p ON p.`player id` = pae.pitcherid
            INNER JOIN insideed.`team table` ptc ON p.`team id` = ptc.`team id`
            INNER JOIN insideed.`player table` AS c ON pae.catcherid = c.`player id`
            INNER JOIN insideed.`team table` tp ON pae.pitcherteamid = tp.`team id`
            INNER JOIN insideed.`team table` th ON pae.hitterteamid = th.`team id`
            INNER JOIN GameDataReport.bscount bs ON bs.bscount = pae.`bscount`
            INNER JOIN GameDataReport.actions act ON act.result = pae.pitchresult
            INNER JOIN locations loc ON pae.pitchlocation = loc.pitchlocation
            
        /*INNER JOIN temppid154245 tpid ON pae.pitcherid = tpid.id*/
       
            LEFT JOIN GameDataReport.events e ON pae.primaryevent = e.iecode
            LEFT JOIN GameDataReport.side_of_field sof ON sof.field_code = pae.eventdirection
            LEFT JOIN GameDataReport.runners r ON r.rob = pae.rob
            LEFT JOIN GameDataReport.subpitchtype spt ON spt.PT = pae.pt
       
      WHERE
            pae.NoRelatedPitch  IS FALSE AND
            COALESCE(pae.PrimaryEvent,'') <> 'X' AND
            gh.Date >= min_date AND
        gh.Date <= max_date AND
            (1=(CASE WHEN pIDs IS NULL THEN 1 ELSE 0 END) Or pae.pitcherid IN (Select id From temppid100)) AND
            (1=(CASE WHEN hIDs IS NULL THEN 1 ELSE 0 END) Or pae.hitterid IN (Select id from temphid100))
        /*(1=(CASE WHEN pIDs IS NULL THEN 1 ELSE 0 END) Or pae.pitcherid IN (pIDs,'\'',''))) AND*/
        group by pae.pitchandeventid
        Order by pae.gameid,pae.recordsequence;
       
call droptemptable ('temppid100');
call droptemptable ('temphid100');

END$$
DELIMITER ;
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 40

Expert Comment

by:Sharath
ID: 41842874
I would suggest you to create the temp table even though the pIDs or hIDs are NULL. How big are your temp tables? If pIDs/hIDs are NULL, you can create empty tables.
Replace this  
IF pIDs IS not NULL THEN 
      Call MakeTempTable ('100',pids,1);
end if;      

IF hIDs IS not NULL then 
      Call MakeTempTable ('100',hids,0);
end if;

Open in new window

with
Call MakeTempTable ('100',pids,1);
Call MakeTempTable ('100',hids,0);

Open in new window


So you won't get 'table does not exist' error. Then you can handle the WHERE clause like this.
WHERE 
    pae.NoRelatedPitch  IS FALSE AND
    COALESCE(pae.PrimaryEvent,'') <> 'X' AND
    gh.Date >= min_date AND gh.Date <= max_date AND
    pae.pitcherid IN (Select id From temppid100) AND
    pae.hitterid IN (Select id from temphid100)

Open in new window

If the temp tables are empty, how do you want to filter the data? Do you want to ignore those filter conditions?
0
 

Author Comment

by:klheitz
ID: 41843014
Hi Sharath ~
Yes if the string is NULL, I want to ignore the filter conditions.

The MakeTable creates a table with a list of numbers (player IDs) and the query filters on them.
If pID = Null, it means use all the pitcherIDs (e.g. don't filter on pitcherId) so I want to skip over the 'pae.pitcherid IN...' line.

Thanks!
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41843025
try like this.
WHERE pae.NoRelatedPitch IS FALSE
  AND COALESCE(pae.PrimaryEvent,'') <> 'X'
  AND gh.Date >= min_date 
  AND gh.Date <= max_date 
  AND ((pae.pitcherid IS NOT NULL AND pae.pitcherid IN (Select id From temppid100))
       OR pae.pitcherid IS NULL)
  AND ((pae.hitterid IS NOT NULL AND pae.hitterid IN (Select id From temphid100))
       OR pae.hitterid IS NULL)

Open in new window

0
 

Author Comment

by:klheitz
ID: 41847604
Hi Sharath ~
I tried the code, it works when pIDs and hIDs <> Null, but if either = NULL I still get the Error 1146 (table temppid100 doesn't exist)

I tried setting a variable with different value depending on whether or not pIDs is NULL; then using that variable in the WHERE clause.
Workbench gave an error unless I used concat() within WHERE itself (see below); but this didn't give the correct results. It returned only records sorted on dates; as if @strH was blank (I verified it was not)

select @min_date, @max_date,@strH,@strP
@min_date, @max_date, @strH, @strP
2015-08-01, 2016-09-18,  AND pae.hitterid IN (Select id From temphid100), <NULL>

Here is the sample code with the attempted concat(..) in WHERE.
(It takes the dates but not the string (???) Maybe I'm doing something incorrect using a string? When I copy it in directly it filters as expected.)

*** sample code ***
use gamedatareport;

call droptemptable ('temppid100');
call droptemptable ('temphid100');

set @hIDs = '31982,15562,20712,17031,19881,18325,20493,31174,18482,20124,15370,28563,15394,29576,18076,20428,7507,15432,14838,18340,31176,10855,29577,14806,20121,29579,29582,8753,13908,14900,10786,15242,18064,31355';
/*set @pIDs = '11242,19727,11474,15000,13900,31550,17980,15400,29957,31478,13392,24997,29284,8009,17793,18415,17332';*/
set @pIDs = NULL;
set @min_date = '2015-08-01';
set @max_date = '2016-09-18';

set @strH = (CASE WHEN @hIDs IS NULL THEN '' ELSE  ' AND pae.hitterid IN (Select id From temphid100)' END);
set @strP = (CASE WHEN @pIDs IS NULL THEN '' ELSE  ' AND pae.pitcherid IN (Select id From temppid100)' END);

/*Call MakeTempTable ('100',pids,1);*/
Call MakeTempTable ('100',@hids,0);

SELECT
            pae.PitchAndEventId,
            gh.`Date` AS Date,
            @strH, @strP
       
      FROM    
            GameDataReport.gameheader gh
            INNER JOIN pitchandevent pae ON pae.gameid = gh.gameid
       
      WHERE
                  pae.NoRelatedPitch IS FALSE AND
                  COALESCE(pae.PrimaryEvent,'') <> 'X' AND
                  concat(gh.Date >= @min_date AND gh.Date <= @max_date, @strH, @strP)
      
        Order by pae.gameid,pae.recordsequence;

call droptemptable ('temppid100');
call droptemptable ('temphid100');

select @min_date, @max_date, @strH, @strP

********** end ***

My next trick was to use a case structure in the WHERE clause. This didn't work either. I got Error 1137 'can't reopen temphid100 table'.

*** sample code w/ CASE in WHERE ***
call droptemptable ('temphid100');

set @hIDs = '31982,15562,20712,17031,19881,18325,20493,31174,18482,20124,15370,28563,15394,29576,18076,20428,7507,15432,14838,18340,31176,10855,29577,14806,20121,29579,29582,8753,13908,14900,10786,15242,18064,31355';
/*set @pIDs = '11242,19727,11474,15000,13900,31550,17980,15400,29957,31478,13392,24997,29284,8009,17793,18415,17332';*/
set @pIDs = NULL;
set @min_date = '2015-08-01';
set @max_date = '2016-09-18';

set @strH = (CASE WHEN @hIDs IS NULL THEN '' ELSE  ' AND pae.hitterid IN (Select id From temphid100)' END);
set @strP = (CASE WHEN @pIDs IS NULL THEN '' ELSE  ' AND pae.pitcherid IN (Select id From temppid100)' END);

/*Call MakeTempTable ('100',pids,1);*/
Call MakeTempTable ('100',@hids,0);

SELECT
            pae.PitchAndEventId,
            gh.`Date` AS Date,
            @strH, @strP, @pIDs, @hIDs
       
      FROM    
            GameDataReport.gameheader gh
            INNER JOIN pitchandevent pae ON pae.gameid = gh.gameid
       
      WHERE
                  CASE True
                        when @pIDs IS NULL AND @hIDs IS NULL then
                              pae.NoRelatedPitch IS FALSE AND
                              COALESCE(pae.PrimaryEvent,'') <> 'X' AND
                              gh.Date >= @min_date AND
                              gh.Date <= @max_date AND
                              pae.hitterid IN (Select id From temphid100) AND
                              pae.pitcherid IN (Select id From temppid100)
                when @pIDs IS NULL then
                              pae.NoRelatedPitch IS FALSE AND
                              COALESCE(pae.PrimaryEvent,'') <> 'X' AND
                              gh.Date >= @min_date AND
                              gh.Date <= @max_date AND
                              pae.hitterid IN (Select id From temphid100)
                        when @hIDs IS NULL then
                              pae.NoRelatedPitch IS FALSE AND
                              COALESCE(pae.PrimaryEvent,'') <> 'X' AND
                              gh.Date >= @min_date AND
                              gh.Date <= @max_date AND
                              pae.pitcherid IN (Select id From temppid100)                
                ELSE
                              pae.NoRelatedPitch IS FALSE AND
                              COALESCE(pae.PrimaryEvent,'') <> 'X' AND
                              gh.Date >= @min_date AND
                              gh.Date <= @max_date
                        END
                  
        Order by pae.gameid,pae.recordsequence;

call droptemptable ('temppid100');
call droptemptable ('temphid100');

select @min_date, @max_date, @strH, @strP

********* end ***

I've searched high and low for other ways to use variable language in a WHERE clause and I am coming up blank.

I'm going to try/time filling the temp tables will all playerIDs if null is passed in. Seems like a lot of unnecessary processing when all I want is to skip the filter if the string isnull.

Any other ideas?
Thanks!
0
 

Accepted Solution

by:
klheitz earned 0 total points
ID: 41867809
Hello All;
I was not able to find any way to condition the WHERE clause correctly. Case, If, putting the condition in SELECT, no luck.
My solution was to pass in in all IDs if all players were selected (vs. NULL) and allowing the SP to run the same regardless of all or some playerIDs.
Thanks for all assistance.
0
 

Author Closing Comment

by:klheitz
ID: 41875256
Changed approach as no solution could be reached.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now