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
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
MySQL ServerMicrosoft Access
Last Comment
k heitz
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
so, actually this is relating to Excel, and not Access?
i may need to do some tests to simulate your issue.
i may need to do some tests to simulate your issue.
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!
Thank you!
Thank you for responding.
My Access query is called EW and sql view looks like -
CALL dbName.storedprocedurename
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