Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL - CASE WHEN use with CAST or defining parameters of data

Posted on 2013-12-11
4
Medium Priority
?
206 Views
Last Modified: 2013-12-17
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
0
Comment
Question by:ghettocounselor
  • 2
4 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39712266
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
 

Accepted Solution

by:
ghettocounselor earned 0 total points
ID: 39712275
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
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39712445
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
 

Author Closing Comment

by:ghettocounselor
ID: 39723480
reason for selecting my own comment: it worked, no one else offered anything.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question