Retrieving a portion of the extendeddata column from the DTREE table

Primavera_General_Account_(primav021_-_(deleted))

I have customized the project node to add new fields. The data in these fields is stored in the ExtendedData column in the DTree table. I want to extract the name of the project and one of the new fields in a livelink report. e.g.ProjectName ProjectRef---------- ----------The problem stems from the fact that the extendedData column is a LONG datatype in the database. Common string handling functions such as SUBSTRING, INSTRING are not supported (atleast by oracle) at the level. Does livelink provide me with a way of extracting this data ? Any comments/suggestions wud be appreciated..

Find more posts tagged with

Comments

John W. Simon, Jr.

We've had a lot of trouble with long data types as well. The only thing that comes to mind is to convert the long to something else (varchar2) before manipulating. So, check out the convert function.Regardsjohn.w.simon@usa.conoco.com

Primavera_General_Account_(primav021_-_(deleted))

The problem is the convert() or tht to_char function do not work on a LONG datatype. A workaround is to use an external reporting tool like Infomaker, Access and connect it to the Livelink database. You can then use the report writers' internal functions (e.g. substr, mid ) on the long datatype. regardsSury

Alex_Kowalenko_(akowalen_(Delete)_2285456)

In Oracle you can add a user-defined function to the schema to convert the first 32,000 characters of ExtendedData Long data into a VarChar2 datatype, example:create function ed_char(selid in number)return varchar2is edc varchar2(32000);beginselect extendeddata into edcfrom dtree where dataid = selid;return(edc);end;Then you can call this function in your LiveReport to manipulate the extendeddata.Here's another function that extracts a named assoc value from extendeddata. This is useful to get attribute values that are stored here, example:create or replace function ed_value(selid in number, valuekey in varchar2)-- Extract an assoc character value from DTree.ExtendedData-- This function searches for ''='' and returns -- Alex Kowalenko 2001 01 02return varchar2is edc varchar2(32000);keytag varchar2(255);vstart number(10);vend number(10);retvalue varchar2(32000);beginselect extendeddata into edc from dtree where dataid = selid;keytag := '''' || valuekey || '''=';vstart := Instr(edc,keytag);If vstart = 0 Then retvalue := null;Elsevstart := vstart + Length(keytag) + 1;vend := Instr(edc,''',''',vstart);If vend = 0 Then vend := Instr(edc,'''>',vstart); End If;If vend >= vstart Then retvalue := replace(substr(edc,vstart,vend-vstart),'\''','''');Else retvalue := null; End If;End If;return(retvalue);end;I used this function in a LiveReport that lists discussion threads by extracting 'Content' assoc values containing discussion comments.