MySQL case statement in WHERE clause - condition fails

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 ?

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

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.

Is it possible to provide your entire query?

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 ;

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;

Select allOpen in new window

with

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

Select allOpen 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)

Select allOpen 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?

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!

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)

Select allOpen in new window

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!

Changed approach as no solution could be reached.