Solved

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

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

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 discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now