如何使用case ---当-----在sql视图中结束 [英] How to use case---when-----end in sql view

问题描述

亲爱的团队，

i希望在找到数字时根据某个数字测试表中的字段，然后相应的值应该替换这些字段，我希望在视图中进行检查

是我到目前为止所做的。



我尝试过的事情：



DECLARE @SQL VARCHAR（4000）

SELECT @ SQL ='创建视图VW_DEMOGRAPHIC为

BEGIN

SELECT DISTINCT TOP（120）REPLACE（a.Lastname，a.Lastname，''Hidden''）作为姓氏

，rREPLACE（a.Firstname，a.Firstname，''Hidden''）作为名字

CASE b.MaritalStatus

当''42''然后''单''

当''43''那么' '结婚''

当''44''然后''WIDOW''

当''47''然后''离婚''

当''189''然后'分开''

结束[婚姻状况]

来自VW_PatientDetail a

加入mst_Patient b on a.Ptn_pk = b.Ptn_Pk GROUP BY a.AGE，a.Lastname，a.Firstname，b.MaritalStatus'

exec（@ sql1）

END



如果我运行选择部分它工作正常但在sql视图中[Marital Status]列导致Null。

i将不胜感激任何帮助解决这个问题

Dear Team,
i want to test a field in a table based on certain number when does number are found then the corresponding values should replace those fields, i want the checking to happen within a view
below is what i have done so far.

What I have tried:

DECLARE @SQL VARCHAR(4000)
SELECT @SQL='CREATE VIEW VW_DEMOGRAPHIC AS
BEGIN
SELECT DISTINCT TOP(120) REPLACE(a.Lastname,a.Lastname,''Hidden'')as Lastname
,rREPLACE(a.Firstname,a.Firstname,''Hidden'')as Firstname
CASE b.MaritalStatus
WHEN ''42'' THEN ''SINGLE''
WHEN ''43'' THEN ''MARRIED''
WHEN ''44'' THEN ''WIDOW''
WHEN ''47'' THEN ''DIVORCE''
WHEN ''189'' THEN ''SEPERATED''
END as [Marital Status]
from VW_PatientDetail a
join mst_Patient b on a.Ptn_pk = b.Ptn_Pk GROUP BY a.AGE,a.Lastname, a.Firstname, b.MaritalStatus'
exec (@sql1)
END

if i run the select part it work fine but within sql view the column [Marital Status] is resulting to Null.
i will appreciate any help to fix this problem

推荐答案

你应该在END之前加入一个else来处理上述情况，请阅读本文

You should include a else before the END to take care of cases that are none of the above,read this

Quote：

ELSE else_result_expression

如果没有比较操作的计算结果为TRUE，则返回表达式。如果省略此参数且没有比较操作计算为TRUE，则CASE从

ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL...

/library/ms181765.aspx\">CASE（Transact-SQL） [ ^ ]

这篇关于如何使用case ---当-----在sql视图中结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！