Make a select from three columns (with case expression)

Question

Post reply

Make a select from three columns (with case expression)

river1

SSC-Insane

Points: 24309
More actions
April 22, 2013 at 8:48 am

#271536

HI,
I have a table (named empresa) which has several fields.
Three of the fields are:
perc_cs_publico
perc_cs_privado
perc_cs_estrangeiro
I want to make a select that returns PUBLICO if perc_cs_publico is different from zero and perc_cs_privado and perc_cs_estangeiro are equal to zero.
It should Return MISTO if perc_cs_publico is different from zero and perc_cs_estrangeiro or perc_cs_privado are different from zero
IT should return PRIVADO if perc_cs_publico is equal to zero and perc_estangeiro or perc_cs_privado is different from zero.
How can I do this select?
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChazMan SSCrazy Points: 2259 More actions · Answer 1

it is a relatively straightforward CASE statement

CASE WHEN perc_cs_publico <> 0 AND perc_cs_privado = 0 AND perc_cs_estrangeiro = 0 THEN PUBLICO

WHEN perc_cs_publico <> 0 AND (perc_cs_privado <> 0 OR perc_cs_estrangeiro <> 0) THEN MISTO

WHEN perc_cs_publico = 0 AND (perc_cs_privado <> 0 OR perc_cs_estrangeiro <> 0) THEN PRIVADO END

Beware, that if there is a record where all three are zero, you'll get a NULL value because you haven't defined a value for that situation.

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 2

I suggest adding a computed column to the table to determine the value: then the definition is only one place, and is thus very easy to change everywhere:

ALTER TABLE dbo.tablename

ADD perc_cs_return AS

CASE WHEN perc_cs_publico <> 0 AND perc_cs_privado = 0 AND perc_cs_estangeiro = 0 THEN perc_cs_publico

WHEN perc_cs_publico <> 0 AND (perc_cs_privado <> 0 OR perc_cs_estangeiro <> 0) THEN perc_cs_misto

WHEN perc_cs_publico = 0 AND (perc_cs_privado <> 0 OR perc_cs_estangeiro <> 0) THEN perc_cs_privado

ELSE NULL END

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

river1 SSC-Insane Points: 24309 More actions · Answer 3

Thank you for your reply but in this case I prefer the first solution.

Thank you all for the help.