Solved

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

Posted on 2013-12-11
4
202 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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 shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

825 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