Link to home
Start Free TrialLog in
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.
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of k heitz
k heitz

ASKER

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?

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

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

ASKER

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!