Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
View called vInsuranceCategory
This view encapsulates the llattrdata structure and puts all the rows for the same objected into one scalar representation.Our main dtree.subtype=31067 gives us only contract file objects
Color Legend
G,A,E,W,S-This comes form category DEFID 49898 or Insurance Category type date
C -This comes from category DEFID 49886 or Basic Operational Binder Category
R - This comes from category DEFID 49889 or Basic Operational Contract Category
USE [intermediate]
GO
/****** Object: View [interlluser].[vInsuranceCategory] Script Date: 12/18/2008 11:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [interlluser].[vInsuranceCategory]
(name,dataid, gen,auto,excess,workers,safety,contractor,region) as
Select t.name, a.id, a.valdate,b.valdate,c.valdate,d.valdate,e.valdate,f.valstr,g.valstr from
LLAttrData a,llattrdata b,llattrdata c,llattrdata d,llattrdata e,llattrdata f,llattrdata g,
DTree t where t.subtype=31067 and
(t.DataID=a.ID and a.DefID=49898 and a.AttrID=13 and t.VersionNum=a.VerNum)and
(t.DataID=b.ID and b.DefID=49898 and b.AttrID=14 and t.VersionNum=b.VerNum)and
(t.DataID=c.ID and c.DefID=49898 and c.AttrID=15 and t.VersionNum=c.VerNum)and
(t.DataID=d.ID and d.DefID=49898 and d.AttrID=16 and t.VersionNum=d.VerNum)and
(t.DataID=e.ID and e.DefID=49898 and e.AttrID=18 and t.VersionNum=e.VerNum)and
(t.DataID=f.ID and f.DefID=49886 and f.AttrID=17 and t.VersionNum=f.VerNum)and
(t.DataID=g.ID and g.DefID=49889 and g.AttrID=58 and t.VersionNum=g.VerNum)
Sample Query against the view
select dataid,contractor,region,gen,auto,excess,workers,safety
from vinsurancecategory
where gen <= '2009-02-19 00:00:00.000'
or auto <= '2009-02-19 00:00:00.000'
or excess <= '2009-02-19 00:00:00.000'
or workers <= '2009-02-19 00:00:00.000'
or safety <= '2009-02-19 00:00:00.000'
order by region