using a len in a LEFT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using a len in a LEFT

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-10-01 : 11:23:35

I am building a lookup table (RANK_DIM) with the follwoing structure:
RANK_KY int 4 NOT NULL
RANK_CD char 9
RANK_NM_EN char 9
RANK_NM_FR char 9
RANK_LVL_CD char 2
RANK_LVL_NM_EN char 9
RANK_LVL_NM_FR char 9
CREATED_BY varchar 15
CREATED_DATE smalldatetime 4 NOT NULL
UPDATED_BY varchar 15
UPDATED_DATE smalldatetime 4 NOT NULL

The following is some sample data from HRMISRANK
AOH03
AR 01
AR 02
CADET
CCM10
CCM11
CCM12
DAC93
DACON 02
DACON 03
LSIM 05
LSPRO 01
LSPRO 02
LSSE 02
ROTARYWIN

The following is the query I am using to populate the RANK_LVL_CD the table.

Select
CASE
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '0%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '1%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '2%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '3%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '4%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '5%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '6%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '7%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '8%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '9%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
END
from workapp..newrank

Now I want to put the alpha part of the hrmisrank into RANK_CD of the RANK_DIM table. Is there any easy way to do this taking into account the different data we have in the hrmisrank column.

I tried using the followingin an update statement, but got the following error: Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Select'.

The update statement is :
UPDATE RANK_DIM
SET RANK_LVL_CD=
Select
CASE
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '0%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '1%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '2%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '3%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '4%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '5%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '6%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '7%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '8%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
WHEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))LIKE '9%' THEN LEFT(LTRIM(HRMISRANK),(len(HRMISRANK)-2))
END
from workapp..newrank

If I run this as a select it works. Anyone know why????

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 11:29:30

don't you need a where clause to indicate which record to update? and if you enclose the select statement in ( ), what happens?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources