Case statement that contains multiplication

Question

Case statement that contains multiplication

cory.bullard76

Ten Centuries

Points: 1232
More actions
August 24, 2015 at 8:13 am

#311099

Hello...I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9.
But, I'm getting a Null value for the answer..here is the statement:
,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"
For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you're getting NULL, either labels.BAGSDISP is NULL or parts.ndc is equal to something other than the literal. There's no ELSE clause of the CASE, so if there's no match, the result is null.

Check with

case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 ELSE -1 end [Units Dispensed]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 2

Thanks...you were right. This patient had a different NDC code. So, I changed the parts.ndc to the correct code and now I'm getting the following error:

Arithmetic overflow error converting varchar to data type numeric.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Check your query, see where you're trying to convert a varchar to a numeric value. See what types are involved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 4

I'm new to SQL...how can I tell? I brought the labels.bagsdisp field into the query. The value is 2. I'm just trying to multiply that times 0.9

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Take each column in the query, look at the base table and check the data type. Identify where you're comparing a varchar to a number, or doing any form of maths on a varchar.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 6

Gotcha...ok, Labels.bagsdisp is a varchar. Would I need to convert that to a number? if so, how would I do that?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It's preferable to do explicit conversions.

What's the range of values in that column (10? 100? 1000? 100000?), and are there any non-numeric values?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

You're already in a good spot for the fix with Gail's excellent advice.

Just for a bit of explanation in case you're curious, that is expected even with just the value '2'.

Prior to the multiplication, SQL Server is trying to convert '2' to the same datatype it gives 0.9, which is numeric(1,1). Trying to convert '2' to numeric(1,1) will fail with that error, since it only allows one digit, and that to the right of the decimal point.

Cheers!

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 9

Gila, I'm not 100% sure what the range is in the Bagsdisp field. I'd imagine up to 100 at the very highest. The numbers that the field is being multiplied against is from 0.67 to 50

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Could you look at the table and find out? Or would you prefer I guess as to the appropriate data type and give you a query that could throw more errors?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 11

The values from labels.bagdisp range from 2-60. Is that what you are asking for?

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 12

Gila...just wanted to follow up to see if you had any luck with this

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

cory.bullard76 (8/24/2015)
The values from labels.bagdisp range from 2-60. Is that what you are asking for?

Gail's asking you to look at the table definition. Easiest way to do that is to click on it in object explorer in SSMS, expand the node out and read the datatype for the column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 14

cory.bullard76

Ten Centuries

Points: 1232

August 25, 2015 at 7:33 am

#1822198

the datatype for that field is a Varchar