SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE view [dbo].[vw_claim_variables]
as


/*
DGE 30/11/2015
Cette vue présente la liste des variables sinitres:
- réelles, donc celles issues de la production (pr_products_variables);
- virtuelles, table nm_claims_variables (variables avec un ID négatif)
*/


select	cv.*,
		nmpv_variable_code, nmpv_l1, nmpv_l2, nmpv_l3                                                         
from	claim_variables cv
		inner join pr_products_variables pv	on pv.prdv_variable_id = cv.clv_variable_id
		inner join nm_products_variables nv on nv.nmpv_variable_code = pv.prdv_variable_code

union	
select	cv.*,
		nmpv_variable_code = nmcv_variable_code,
		nmpv_l1 = nmcv_l1, 
		nmpv_l2 = nmcv_l2, 
		nmpv_l3 = nmcv_l3
from	claim_variables cv
		inner join nm_claims_variables nv	on nv.nmcv_variable_id = cv.clv_variable_id






GO
