sql server - Is it possible to do a join on a user-calculated/custom SQL view column? -
this sql server
so have sql view manually define values of custom column via case statement. let's call column name statusid
.
now, have mapping table holds statusid
, description
. let's call table dbo.statusmapping
. possible join on statusmapping.statusid
, custom view column "statusid
" can obtain description
can add view?
example code:
select distinct(a.overallid), case when a.istrue null 1 else 2 end statusid, --this i'm trying join table on b.description dbo.[maintable] join dbo.[statusmapping] b on b.statusid = statusid --this fails. "invalid column name 'statusid'"
you have several options join:
- repeat expression computed column in
where
clause - not ideal, because same expression repeated, or - use common table expression - work sql server, not work in mysql , other rdbms not support cte, or
- make nested
select
table alias - lets avoid repetition.
here example of how use nested select
:
select distinct(x.overallid), x.statusid, b.description ( select a.overallid, case when a.istrue null 1 else 2 end statusid dbo.[maintable] ) x join dbo.[statusmapping] b on b.statusid = x.statusid
note: not sure of purpose of distinct
around a.overallid
in original query. copied mechanically query, chances going unnecessary, or need replace group by
.
Comments
Post a Comment