Report from DTree, KUAF and LAttrData

Andrew_Coyle

Hi All, I'm attempting to write a report that needs to pull data from a few different tables to get the correct output. My table needs to be able to provide a document name, the value of a date stored as a cateogry attribute to the document and the current owner of the document (also from a cat. attr and not from Dtree which as a result of other processes will always show Admin). Heres the SQL I'm using that I think is almost complete:________________________________________________________________________________________________SELECT attrdata.[ID] ,dtree.[Name] as "Name" ,kuaf.[Name] as "Owner" ,attrdata.[VerNum] as "Latest Version" ,attrdata.[DefID] ,attrdata.[DefVerN] ,attrdata.[AttrID] ,attrdata.[AttrType] ,attrdata.[CustomID] ,attrdata.[EntryNum] ,attrdata.[ParentKeyID] ,attrdata.[KeyID] ,attrdata.[ValInt] ,attrdata.[ValReal] ,attrdata.[ValDate] ,attrdata.[ValStr] ,attrdata.[ValLong]FROM [livelinkprod].[ll_dbuser].[LLAttrData] attrdata -- Get the filename LEFT OUTER JOIN [livelinkprod].[ll_dbuser].[DTree] dtree ON attrdata.[ID] = dtree.DataID -- Get the Username LEFT OUTER JOIN [livelinkprod].[ll_dbuser].[KUAF] kuaf ON (Select ValInt From [livelinkprod].[ll_dbuser].[LLAttrData] Where AttrID = '6' AND VerNum = (Select MAX(VerNum) from [livelinkprod].[ll_dbuser].[LLAttrData]) ) = kuaf.[ID]WHERE AttrID = '10' AND (ValDate > CAST('2009-12-01 00:00:00.000' AS datetime) AND ValDate < CAST('2011-12-31 00:00:00.000' AS datetime))__________________________________________________________________________________________Now as it stands that code doesn't work. As is shown in the attached .xls file the LLAttrData stores a number of different attributes for the same document on different rows and this is where my limited knowledge of SQL makes things difficult. AttrID 10 in LLAttrData (hereon referred to as attrdata) is a date range I need to query to see if the doc falls within it, if it does I also need AttrID 6 to make up the user that owns the document. The problem comes with the subquery portion of the above code that either produces an error or produces unexpected results either showing the Owner column (kuaf.Name) with all records being NULL or all appearing as a single user. Can anyone help with this?

Appu_Nair

I would definitely take a look at this article.This makes it extremely easy to make that particular category flattened as a viewhttps://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=3498953&objAction=ArticleViewYou say this "the LLAttrData stores a number of different attributes for the same document on different rows and this is where my limited knowledge "Once you have the view you will be so amazed at how much cleaner your query looks and belive me every time I try to go the route of not creating the view I always end up coming back to the view creation.BTW:If you have bought OpenText BI the same view method they have done differently using a UDF that works well as well.