ghettocounselor
asked on
SQL - CASE WHEN use with CAST or defining parameters of data
I have a situation where I need to check the character of one variable and then store a variable which I'd like to store as a number (or something I can later sum up in another query of the data) but I'm having some trouble with this step.
How do I define the characteristics of DISP_QTY in this example.
CASE WHEN PRICE > 0 THEN '5' ELSE '-5' END ???VARCHAR()??? as DISP_QTY
How do I define the characteristics of DISP_QTY in this example.
CASE WHEN PRICE > 0 THEN '5' ELSE '-5' END ???VARCHAR()??? as DISP_QTY
you can probably define 2 variables with different data types and use them in a case stmt to assign the value to DISP_QTY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may allow the RDBMS to implicitly choose a data-type:
-- char
DISP_QTY = CASE WHEN PRICE > 0 THEN '5' ELSE '-5' END
-- integer
DISP_QTY = CASE WHEN PRICE > 0 THEN 5 ELSE -5 END
You can explicitly cast into the data-type you want, lets say I want money
(note: in this example, quotes may be kept or eliminated):
DISP_QTY = cast( CASE WHEN PRICE > 0 THEN '5' ELSE '-5' END as money )
ASKER
reason for selecting my own comment: it worked, no one else offered anything.