-= assigning value lables through a linked table =-

[Guest_imported]

I am new to MS SQL server. In MS Access I use a linked table to assign labels to values ((we have a huge database of only numeric codes in each field... the linked table assigns values to the numeric codes exp: table1 field1 values range from 1 to 4 ... the linked table - assigns 1 = very new, 2 = somewhat new, 3 = somewhat used, used))

How is this done in MS SQL Server?

 

[dbtech]

This is the query
SELECT CASE WHEN table1.field1 = 1 'Very New' END,
CASE WHEN table1.field1=2 'somewhat new' END,
CASE WHEN table1.field1=3 'somewhat used' END,
CASE WHEN table1.field1=4 'used' END
FROM table1

see SQL Server BOL for more syntax on CASE statement

dbtech

 

[tlbroadbent]

When you say "linked table" do you mean a JOINed table? It seems that is what you describe. If that is the case, then you can do the same thing in SQL Server. The CASE staement mentioned by dbtech is a good tool when the number of items you need to consider is small and static. However, using a table to hold the Descriptions of the numeric codes is more flexible and scalable.

Select a.NumCd, b.CdDescription, ....
From MainTbl a Inner Join DescTbl b
On a.NumCd=b.NumCd Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin