populate field in view conditionally

[voxinforma]

I am creating a view from table A, and also have table B and C. If A.departmentcode=1 I want to populate the field (as alias, call it xyz) with the corresponding field B.specialcode where A.objectid=B.objectid, otherwise I want xyz to contain the value of the field C.supercode where A.objectid=C.objectid.

So far, have not hit on a good way to do this. Any help would be appreciated. Thanks.

 

[tlbroadbent]

Use the CASE function.

Select
<columns list>,
xyz=
Case When a.departmentcode=1
Then b.specialcode
Else c.Supercode
End
From tableA a
Join tableB b --could be Inner or Left Join
On a.objectid=b.objectid
Join TableC c --could be Inner or Left Join
On a.objectid=c.objectid Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[voxinforma]

perfect! thanks.