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.
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'
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