视图创建,请求支援
有一个表和两个mast1， mast2
tb1
CODE1 CODE2 CODE3
PS10001 PS10001 PD10001
PS10002 PS10004 PD10002
PS10003 PS10005 PD10003

mast1
CODE NEW_CODE
PS10001 DS10001
PS10002 DS10002

mast2
CODE NEW_CODE
PD10001 DD10001

想在view里面将CODE1和CODE2转换成NEW_CODE,如果没有对应的NEW_CODE，
就将原来的code写在相应的列里面，然后在后面加上一个flag

view1
CODE1 CODE2 CODE3 CD_CNV_STS
DS10001 DS10001 DD10001
DS10002 PS10004 PD10002 1
PS10003 PS10005 PD10003 1

请大家伸出援手，帮帮忙


[解决办法]
Create Table tb1
(CODE1Varchar(10),
CODE2Varchar(10),
CODE3Varchar(10))
Insert tb1 Select 'PS10001 ', 'PS10001 ', 'PD10001 '
Union All Select 'PS10002 ', 'PS10004 ', 'PD10002 '
Union All Select 'PS10003 ', 'PS10005 ', 'PD10003 '

Create Table mast1
(CODEVarchar(10),
NEW_CODEVarchar(10))
Insert mast1 Select 'PS10001 ', 'DS10001 '
Union All Select 'PS10002 ', 'DS10002 '

Create Table mast2
(CODEVarchar(10),
NEW_CODEVarchar(10))
Insert mast2 Select 'PD10001 ', 'DD10001 '
GO
Create View V_TEST
As
Select
IsNull(B.NEW_CODE, CODE1) As CODE1,
IsNull(C.NEW_CODE, CODE2) As CODE2,
IsNull(D.NEW_CODE, CODE3) As CODE3,
(Case When B.NEW_CODE Is Null Or C.NEW_CODE Is Null Or D.NEW_CODE Is Null Then 1 Else 0 End) As CD_CNV_STS
From
tb1 A
Left Join
mast1 B
On A.CODE1 = B.CODE
Left Join
mast1 C
On A.CODE2 = C.CODE
Left Join
mast2 D
On A.CODE3 = D.CODE
GO
Select * From V_TEST
GO
Drop Table tb1, mast1, mast2
Drop View V_TEST
GO
--Result
/*
CODE1CODE2CODE3CD_CNV_STS
DS10001DS10001DD100010
DS10002PS10004PD100021
PS10003PS10005PD100031
*/