Case When

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings everyone. I need help with a stored procedure. I need to add a
column to enter a value, either 1,2, or 3 depending on the value of another
field in the query. For example, if the value of the independent field is
'apple' I need the new dependent field to populate '2'. I think I need to do
a case when statement, but I can not get it to work. Is this anywhere close?

case when type_of_fruit= 'apple' then '2'

Do I enter this directly into the column field of the grid pane? Thank you
for any advice or help.

Greg Snidow
 
You are missing the End statement at the end of your Case; here is a
complete example:

Select Case when type_of_fruit = 'apple' then '2' when type_of_fruit =
'orange' then '3' else '0' end as ColorNumber, ...

For simple comparaisons like this, you can also write:

Select Case type_of_fruit when 'apple' then '2' when 'orange' then '3' else
'0' end as ColorNumber, ...
 
Hello Greg,
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 16
Aug 2006 13:14:02 -0700:

GS> Greetings everyone. I need help with a stored procedure. I need to
GS> add a column to enter a value, either 1,2, or 3 depending on the value
GS> of another field in the query. For example, if the value of the
GS> independent field is 'apple' I need the new dependent field to populate
GS> '2'. I think I need to do a case when statement, but I can not get it
GS> to work. Is this anywhere close?

Generally, for the purpose like this the right approach is not hardcoding
the values, but creating a separate table Fruits:

fruit fruitId
apple 2
orange 3

and getting the value by lookup: select fruitId from fruits where
fruit='apple'

The advantage of this is that later, when you add new fruit, you do it by
adding new row to the Fruits table. Otherwise, you will have to find all
occurences of apples etc. in all places (in the database and in the
applications) where you hardcoded the values - and with the time, there will
be many.

Of course, it's kinda general guidance, for educational purposes. If it's
small one-time piece without the future, CASE is ok.


Vadim Rapp
 
Thank you Sylvain. That worked fine with only one record type, but when I
tried to put two in I get the error 'can not parse expression'. I am also
using LIKE if that makes a difference. This one works fine:

CASE WHEN Type_of_Hub LIKE '% Mount 432' THEN '3' END

The hub is a fiber distribution panel which can either be pole mount or pad
mount, and can have 144, 216, 288, or 432 ports. For 'Pole Mount 216' and
'Pad Mount' 216' I want to assign the number 2, the number of fiber splitters
I need to order for the hub. The '%' worked fine when I tried only 'Pad
Mount 432' and 'Pole Mount 432'. then I tried to do this.

CASE WHEN Type_of_Hub LIKE '% Mount 432' THEN '3' WHEN LIKE '% Mount 2%'
THEN '2' ELSE '1' END

The second '%' in '% Mount 2%' is because I want to include both 216 and 288
port hubs. Basically if the hub has 432 ports I want to assign '3', if it
has 216 or 288 ports I want to assign '2', and 144 ports I want to assign
'1'. Also, how do I determine the datatype of the expression column? Thank
you so much for taking time out of your day to help. Thanks to you too
Vadim, and this is a one time thing.
 
Try:

CASE WHEN Type_of_Hub LIKE '% Mount 432' THEN '3' WHEN Type_of_Hub LIKE '%
Mount 2%'
THEN '2' ELSE '1' END
 
Thank you again Sylvain, that worked. My last problem with this query is
that I need to create anothor column to multiply the 1,2,or 3 by the number
of each type of Hub I have when I group. For example, if I have 10 'Pad
Mount 432' in Arlington, and each hub gets 3 splitters as is calculated using
the case when statement, I need a column to multiply the '3' by the 10, which
comes from a count column. Does this make sense?
 
There is no problem multiplying the result of a Case statement (but it would
be a good thing to replace string values like '2' with the integer
equivalent « 2 » ); however, you will have to repeat the whole case
statement inside the expression for the second column as you cannot
reference the result of a calculated column into another column without
using subqueries.
 
Back
Top