Select statement using Case - 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)

Select statement using Case

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-02-23 : 04:24:57

Is there a more elegant way to replace the statement below

Select distinct storecode, itemcode, 'Location' = Ltrim(Rtrim(CASE WHEN Shed='N/A' THEN ''
ELSE + ' ' + Shed + ' ' +
Case level1 when 'N/A' then ''
Else ' ' + Level1 + ' ' +
Case Bay when 'N/A' then ''
Else ' ' + Bay + ' ' +
Case Row when 'N/A' then ''
Else ' ' + Row + ' ' +
Case Rack when 'N/A' then ''
Else ' ' + Rack + ' ' +
Case Shelf when 'N/A' then ''
Else ' ' + Shelf + ' ' +
Case Tray when 'N/A' then ''
Else ' ' + Tray + ' '
End
End
End
End
End
End
END))
FROM <tablename> where <condition>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-23 : 06:14:05

Try

Select distinct storecode, itemcode, replace(replace(Shed+level1+bay+...tray,' ',''),'N/A','') from
<tablename> where <condition>

Madhivanan

Failing to plan is Planning to fail