tags:

views:

32

answers:

4

Hello, my query returns a column that can hold types of real estate. Values can be condo or duplex or house and so on. Instead of displaying condo, I just want a C in the column. My plan was to use a huge case/when structure to cover all the cases, is there an easier way? Just displaying the first letter in upper case wont work by the way, because sometimes that rule cant be applied to create the short code. Duplex for example is DE...

Thanks :-)

+2  A: 

Helper table with a column for each shorthand matching the long string?

Tobiasopdenbrouw
+3  A: 

If you don't want to use a CASE statement how about creating a lookup table to map the column value to the lookup code you want. Join on to this in your query.

NB - Only worth considering if your query is running over a fairly small resultset or you'll hit performance issues. Indexing the column would help.

Some other options are depending on your DB server features:

  1. Use a UDF to do the conversion.
  2. Computed column on the source table.
Chris W
Yup, this is the way I'd go. See my answer to a not dissimilar question for a worked example: http://stackoverflow.com/questions/3254170/sql-server-setting-multiple-values-in-a-case-statement/3254467#3254467
Matt Gibson
A: 

The obvious thing to do would be to have another table which maps your value to a code, to which you can then join your results. But it smells a bit wrong. I'd want to join this other table to key values, not strings (which I assume aren't key values)

Yellowfog
A: 

Why dont you use a decode function in sql

select decode(your_column_name,"condo","C",your_column_name) from table
Vijay Sarathi
Because it does exactly the same thing as a CASE structure, but is somewhat less programmer-friendly.
Mark Bannister
if you are getting the result in an easy way then why should'nt we prefer this?.and its makinga programmers life easy
Vijay Sarathi