Avatar of k heitz
k heitz
 asked on

Using Case or If statement in Select statement, result is a string. Need a number.

Hi Experts;
I have a stored procedure with this case statement in the select query:
(CASE WHEN pae.pitchinzone = 0 AND act.`swungon` = 1 THEN 1 ELSE 0 END) AS Chase,

The stored procedure is called via a pass-thru query in MSAccess.
Problem is the Chase field evaluates to a string and I need it to be numeric.
I've tried cast, convert, +0 on THEN/ELSE values - no success.

I tried switching to an If statement:
cast(if (pae.pitchinzone = 0 and act.swungon = 1,1,0) as unsigned) as chase,
still no success.

The underlying fields are:
pae.pitchinzone is a tinyint, and act.swungon is also an int(11).

I have 3 'formula' fields that are returning strings where I need numbers.

Any advice would be greatly appreciated.
MySQL ServerMicrosoft Access

Avatar of undefined
Last Comment
k heitz

8/22/2022 - Mon
Ryan Chong

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
k heitz

Hi Ryan;
Thank you for responding.

My Access query is called EW and sql view looks like -
CALL dbName.storedprocedurename('2016-04-03','2016-04-29', '28563')

I have a connection on my local machine to MySql server (where dbName sits).
The call returns the records to populate the query.

EW gets used in an Excel file (pivot table). I currently don't have access to that file. I was hoping to be able to force the fields to be numeric before the excel file pulled from the EW query. Does that make sense?

Ryan Chong

so, actually this is relating to Excel, and not Access?

i may need to do some tests to simulate your issue.
k heitz

Hi Ryan - you were spot on. We altered the query in the Excel connection string to use VAL(field1) as field1 and it worked perfectly.
Thank you!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes