SELECT c.officecode  AS doc, 

       CASE 
         WHEN typecode IN ( 1, 18 ) THEN reportsto 
         WHEN typecode IN ( 13 ) THEN officecode 
         ELSE NULL 
       END     AS dist, 

       c.region AS reg, r.regionacronym, 

       CASE 
         WHEN area < 10 THEN '0' + CAST(area AS CHAR(1)) 
         ELSE CAST(area AS CHAR(2)) 
       END  AS area, 

       REPLACE(c.name, ',', '') AS doc_name, 
       r.regionnumber  AS region_num, r.regionname, 
       c.typecode 
FROM   offices.dbo.officecodes AS c 
       INNER JOIN offices.dbo.regions AS r 
         ON c.region = r.regionletter 
WHERE  ( c.typecode IN ( 1, 18, 13 ) ) 

CREATE TABLE [dbo].[OfficeCodes](
	[OfficeCode] [char](3) NOT NULL,
	[Name] [varchar](50) NULL,
	[TypeCode] [int] NULL,
	[ReportsTo] [char](3) NULL,
	[Region] [char](1) NULL,
	[Area] [int] NULL)



insert into OfficeCodes
select '001', 'Portland ME', '13','H03', 'A', '3' union all
select '002', 'BanGor ME', '13', 'H03','A', '3' union all
select '003', 'Augusta ME', '13', 'H03', 'A', '3' union all
select '004', 'Auburn ME', '1', '001', 'A', '3' union all
select '005', 'Rockland ME', '1', '002', 'A', '3' union all
select '006', 'Meadville PA', '1', '205', 'c', '4' union all
select '638', 'Houghton MI', '18', '371', 'E', '1' union all
select '672', 'Key West FL', '18', '261', 'D', '2' union all
select 'A02', 'Globe AZ', '18', '912', 'J', '8' 

quote:

---

Originally posted by JJ297

Two more questions...what do you mean that case is an expression.

---