Prev: Jet uses Yellowfin for Location Intelligence
Next: DTS or Stored Procedure?
From: Gert-Jan Strik on 8 Feb 2010 16:13
RRR wrote:
>
> >
> > Are you sure you correctly stated the original problem? I also wonder if
> > there was a reason the original values were stored in a table.
>
> I, too, was concerned about that. If this were to be used as a means
> of transforming any score to the correct grade, then the CASE approach
> shown would have to specify all possible expected score values. If
> that is the case, then the CASE statement should be more generalized
> so as to map a range of scores to a letter grade.

In this case a CASE expression would not be my first choice. My first
choice would be to create a table (in case one doesn't already exist)
with a mapping between the different grade types (or range of grade
types).

In its simplest form:

CREATE TABLE grades
(grade_number tinyint NOT NULL PRIMARY KEY
,grade_letter char(1) NOT NULL
)
INSERT INTO grades VALUES (50,'F')
INSERT INTO grades VALUES (60,'D')
INSERT INTO grades VALUES (70,'C')
INSERT INTO grades VALUES (80,'B')
INSERT INTO grades VALUES (90,'A')
INSERT INTO grades VALUES (100,'A')

SELECT ..., grades.grade_letter
FROM my_table
JOIN grades
ON grades.grade_number = my_table.grade

I think that for this type of applications, the solution with a CASE
expression should be reserved for ad-hoc selections, which I don't think
this is.

--
Gert-Jan
From: SQL Learner on 8 Feb 2010 19:53
Thank you for your feedback and help, guys.

The one-column table was used only for the purpose of simplication.
My objective was to know how to get a second column which contains the
converted values from the first column. The application can be used
for many occuations. For one, we can use it to convert, say "NY",
"CA" .... to "New York", "California"...

What Plamen provided was enough for the purpose.

I think in term of efficient, Gert-Jan's method may be the best if I
have a lot of data to "convert".

SQL Server
From: Gert-Jan Strik on 9 Feb 2010 12:11
SQL Learner wrote:
>
> Thank you for your feedback and help, guys.
>
> The one-column table was used only for the purpose of simplication.
> My objective was to know how to get a second column which contains the
> converted values from the first column. The application can be used
> for many occuations. For one, we can use it to convert, say "NY",
> "CA" .... to "New York", "California"...
>
> What Plamen provided was enough for the purpose.
>
> I think in term of efficient, Gert-Jan's method may be the best if I
> have a lot of data to "convert".
>
> SQL Server

IMO, data should be in a database, not in a query. The example you give
in this post is a typical example of a states table.

What I would like to add is that you don't want to have the same CASE
expression in multiple places, because that makes it very hard to
maintain. So I would advise against creating lookup tables in
expressions. On the other hand, a CASE expression can be a great
solution for data formatting or adding default descriptions.

--
Gert-Jan
First  |  Prev  | 
Pages: 1 2
Prev: Jet uses Yellowfin for Location Intelligence
Next: DTS or Stored Procedure?