How to generate one view from two views (using the prev one) ?

Question

Hi All,

Need help with making the two view into one :
I have two views se_oos_qr and se_oos_qr_final . How can I make it in one view i.e se_oos_qr ? I have use the se_oos_qr to produce se_oos_qr_final

Here is my script sample :

create view se_oos_qr as
WITH tmp1 AS
(
select case when a is not null the a else b end as MRN,
NAP_OOS.*
FROM NAP_OOS
LEFT JOIN MAPPING
ON NAP_OOS.id=MAPPING.id
)
SELECT
ISNULL([VIEW_REF_SERVICE_UNIT_FACILITY_CHANGE].[Reporting Facility ID],
ISNULL(VIEW_CLINIC_MAPPING.[Reporting Facility ID]
from tmp1

///////////////

Create view se_oos_qr_final as
SELECT [Reporting Facility ID]
,[Facility Identifier]
,[AUID]
,convert(varchar(50),rtrim(se_oos_qr_auid.masked_auid)) as [Masked AUID]
,[AUID_RAW_FOR_AUDIT] as [AUID Raw For Audit]
,[MRN]
FROM se_oos_qr
CROSS APPLY tvf_mask_auid(se_oos_qr.AUID) as se_oos_qr_auid
CROSS APPLY tvf_mask_mrn(se_oos_qr.MRN) as se_oos_qr_mrn

Thanks

Answer 1

Hi @ASHMITP ,

Try this query:

 create view se_oos_qr as  
WITH tmp1 AS  
(  
select case when a is not null then a else b end as MRN,  
NAP_OOS.*  
FROM NAP_OOS  
LEFT JOIN MAPPING  
ON NAP_OOS.id=MAPPING.id  
),temp2 as (SELECT  
ISNULL([VIEW_REF_SERVICE_UNIT_FACILITY_CHANGE].[Reporting Facility ID],  
ISNULL(VIEW_CLINIC_MAPPING.[Reporting Facility ID]  
from tmp1  
)SELECT [Reporting Facility ID]  
,[Facility Identifier]  
,[AUID]  
,convert(varchar(50),rtrim(se_oos_qr_auid.masked_auid)) as [Masked AUID]  
,[AUID_RAW_FOR_AUDIT] as [AUID Raw For Audit]  
,[MRN]  
FROM temp2  
CROSS APPLY tvf_mask_auid(se_oos_qr.AUID) as se_oos_qr_auid  
CROSS APPLY tvf_mask_mrn(se_oos_qr.MRN) as se_oos_qr_mrn  

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".