Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Table Lookup / Cats / Attrs

Status
Not open for further replies.

oscript

Programmer
Mar 25, 2003
63
GB
I have a query which exceeds the length allowed in the query lookup and whatever I do I cannot shorten it. Anyone know a way around the limit ?
 
Ot for this reason asks to to encapsulate
The query into a view which is tying the
Current dtree version object
To the llattrdata.id .are you able to do
It this way.a package called easy lapi
Allows the view creation automatically
As well although it is very easy to do by hand

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010
 
Thanks AppNair - are you able to elaborate a little or perhaps to point me to the Knowkledge Center article or hint by any chance ?
 
I think AppNair's suggestion is to create a View with your SQL in your database and then SELECT from the view in the Table Key Lookup to pull out the values you need.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006
 
here's that article in KB.

based on that I used this method to pull from three different category objects for a project.Going without the view was a nightmare
Code:
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)
Once I created the view it was easy to query
Code:
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


Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top