CHOOSing a value

Question

CHOOSing a value

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
January 6, 2020 at 12:00 am

#3706128

Comments posted to this topic are about the item CHOOSing a value

Viewing 11 posts - 1 through 10 (of 10 total)

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Thanks for sharing! This is an interesting question to start the week. For those starting work after the holidays - Have a Happy New Year 2020!

Reading the documentation and some testing made me realize an interesting point - the return type for CHOOSE works a bit counter-intuitively.

The documentation states that the return type is the highest precedence from the supplied set. The example supplied has string, integer and decimal data. The highest precedence therefore is decimal and one can expect an error because the string supplied cannot be converted to a decimal implicitly.

However, testing reveals that an error is encountered only when the index (@i = 6) - meaning only when CHOOSE actually tries to return the string value to the caller.

To summarize: a determination of the return data-type is done based on the data in the entire set but actual data-type conversion is done only when that element is accessed based on the supplied index.

This reply was modified 4 years, 10 months ago by Nakul Vachhrajani.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 2

Nice question to start the week on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

paul s-306273 SSChampion Points: 10730 More actions · Answer 3

Saw the 3, answered 3.

Only noticed it was 3.14 later.

Bad start to the week.

Thom A SSC Guru Points: 99130 More actions · Answer 4

Think it would be worth mentioning in the Explanation that the return type is determined by data type precedence; in case someone wonders why it isn't 3, due to the data type of the first possible value being an int.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Saurabh.D Ten Centuries Points: 1164 More actions · Answer 5

Very useful function for "Status" column.

thanks Nakul Vachhrajani for return type testing.

ThanksSaurabh.D

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 6

Saurabh.D wrote:

Very useful function for "Status" column.
thanks Nakul Vachhrajani for return type testing.

I would think that an actual join with a lookup table would be better so that you don't have to maintain a list of Statuses in multiple places in code, especially since a new status can crop up at any time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 7

nice question

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 8

For the sake of completeness, CHOOSE() ignores errors too, until you access this value:

-- works well
DECLARE @i INT = 4;
SELECT CHOOSE(@i, 10,20,30,3.14,50,'Test', 1/0)
GO
-- returns division by zero error
DECLARE @i INT = 7;
SELECT CHOOSE(@i, 10,20,30,3.14,50,'Test', 1/0)

On the other hand the same stuff would work with a simple case too:

DECLARE @i INT = 4; -- works for all but 6 and 7
SELECT CASE @i WHEN 1 THEN 10
               WHEN 2 THEN 20
               WHEN 3 THEN 30
               WHEN 4 THEN 3.14
               WHEN 5 THEN 50
               WHEN 6 THEN 'TEST'
               WHEN 7 THEN 1/0
        END

God is real, unless declared integer.

Thom A SSC Guru Points: 99130 More actions · Answer 9

Thomas Franz wrote:

For the sake of completeness, CHOOSE() ignores errors too, until you access this value

I suspect that because it uses the same logic as a CASE expression, which does the same. The below runs fine, despite it looking complete non-sensical:

SELECT CASE 1 WHEN 1 THEN 1
              WHEN 2 THEN 7.4
              WHEN 3 THEN 1/0
              WHEN 4 THEN 'abc'
              ELSE 'spagetti'
       END;

Of course, if you change it to CASE 3 (or 4 or 5) it goes wrong.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

Bit of a funny one

😎

Extract from the execution plan (input value = 1)

(Scalar Operator(CASE WHEN [@i]=(1) THEN (10.00) ELSE CASE WHEN [@i]=(2) THEN (20.00) ELSE CASE WHEN [@i]=(3) THEN (30.00) ELSE CASE WHEN [@i]=(4) THEN (3.14) ELSE CASE WHEN [@i]=(5) THEN (50.00) ELSE CASE WHEN [@i]=(6) THEN CONVERT_IMPLICIT(numeric(4,2),'Test',0) ELSE NULL END END END END END END))