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
ghettocounselorPharmacy Systems AdminAsked:
Who is Participating?
 
ghettocounselorConnect With a Mentor Pharmacy Systems AdminAuthor Commented:
Looks like one approach is to handle this in the subsequent query.

Leaving the above as:
CASE WHEN PRICE > 0 THEN '5' ELSE '-5' END DISP_QTY

And doing the add as:
sum(CAST(DISP_QTY AS INT)) AS PT_DISP_QTY
0
 
dannygonzalez09Commented:
you can probably define 2 variables with different data types and use them in a case stmt to assign the value to DISP_QTY
0
 
John_VidmarCommented:
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 ) 

Open in new window

0
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
reason for selecting my own comment: it worked, no one else offered anything.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.