nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

LiveReport - Task Comments and Instructions

Shelly_Vaughn

LL 9.5 SP1 - Oracle DB. Hi. I'm looking for SQL query to extract the Task Comments and Instructions from the EXTENDEDDATA field from the DTREE table. Any help would be greatly appreciated.

Find more posts tagged with

Comments

David_Beliveau_(sanmin013_-_(deleted))

Message from <Dave.Beliveau@sanmina-sci.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: LiveReport - Task Comments and Instructions

We use this for our MS SQL 2000 database, maybe you can use it as a guide for your oracle db...

select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent, a.name as Task, CAST(SUBSTRING(a.extendedData,CHARINDEX('Instructions''=',a.extendedData)+15,CHARINDEX('>',a.extendedData) - CHARINDEX('Instructions''=',a.extendedData)- 16) as text) AS Instructions, CAST(SUBSTRING(a.extendedData,19, CHARINDEX ('Instructions''=',a.extendedData)-22) as text) as Comments, KUAF.Name as Owner, a.DateDue as Due from DTree a left outer join kuaf on a.assignedto=kuaf.id, Dtree b where a.parentid=b.dataid and a.SubType=206 and((a.Status=0) or (a.Status=1) or (a.Status=2) or (a.Status=3) or (a.Status=-1) or (a.Status=-2)) and (a.OwnerID IN (9476415,9536704)) order by a.ownerID DESC, a.status

Just update the OwnerID references to match your task list.

Dave

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, May 17, 2007 10:29 AM
To: eLink Recipient
Subject: LiveReport - Task Comments and Instructions

LiveReport - Task Comments and Instructions Posted by Vaughn, Shelly on 05/17/2007 10:20 AM

LL 9.5 SP1 - Oracle DB. Hi. I'm looking for SQL query to extract the Task Comments and Instructions from the EXTENDEDDATA field from the DTREE table. Any help would be greatly appreciated.

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677

Livelink Server:
https://knowledge.opentext.com/knowledge/llisapi.dll

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING. Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina-SCI Corporation (or any of its subsidiaries), or any other person or entity.
_____________________________________________________________________________
Scanned by Sanmina-SCI eShield _____________________________________________________________________________

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Please see this posting:

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=4959431&objAction=view

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, May 17, 2007 10:27 AM
To: eLink Recipient
Subject: LiveReport - Task Comments and Instructions

LiveReport - Task Comments and Instructions

Posted by shellyvaughn (Vaughn, Shelly) on 05/17/2007 10:20 AM

LL 9.5 SP1 - Oracle DB. Hi. I'm looking for SQL query to extract the Task Comments and Instructions from the EXTENDEDDATA field from the DTREE table. Any help would be greatly appreciated.

Thorsten Schulte

Hi, I tried to change the report for the use on an oracle DB.But I got an [ora-00906 missing missing left parenthesis] error.But I don't find a bug In my code...=====================================select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent, a.name as Task, CAST(SUBSTR(a.extendedData,INSTR('Instructions''=',a.extendedData)+15,INSTR('>',a.extendedData) - INSTR('Instructions''=',a.extendedData)- 16) as varchar2) as Instructions, CAST(SUBSTR(a.extendedData,19, INSTR ('Instructions''=',a.extendedData)-22) as varchar2) as Comments, KUAF.Name as Owner, a.DateDue as Due from DTree a left outer join kuaf on a.assignedto=kuaf.id, Dtree b where a.parentid=b.dataid and a.SubType=206 and((a.Status=0) or (a.Status=1) or (a.Status=2) or (a.Status=3) or (a.Status=-1) or (a.Status=-2)) order by a.ownerID DESC, a.statusCan somebody help me?thorsten.

Lindsay_Davies

Message from Lindsay Davies via eLinkHi Thorsten,You are missing part of the CAST construct....See http://www.techonthenet.com/oracle/functions/cast.phpWhere you are casting to Varchar2, you need to say how many characters.For example Cast(.... As VarChar2(30))So if you add an appropriate number in brackets after each "as varchar2"then your statement will run.RegardsLindsay-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 06 December 2007 12:35To: eLink RecipientSubject: changing report to oraclechanging report to oraclePosted by Schulte, Thorsten on 12/06/2007 07:27 AMHi, I tried to change the report for the use on an oracle DB.But I got an [ora-00906 missing missing left parenthesis] error.But I don't find a bug In my code...=====================================select (CASE a.status When 0 Then 'Pending' When 1 Then 'In Process'When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When-2 Then 'Cancelled' Else 'Unknown' End) as Status, b.name as Parent,a.name as Task,CAST(SUBSTR(a.extendedData,INSTR('Instructions''=',a.extendedData)+15,INSTR('>',a.extendedData) - INSTR('Instructions''=',a.extendedData)- 16)as varchar2) as Instructions, CAST(SUBSTR(a.extendedData,19, INSTR('Instructions''=',a.extendedData)-22) as varchar2) as Comments,KUAF.Name as Owner, a.DateDue as Due from DTree a left outer join kuafon a.assignedto=kuaf.id, Dtree b where a.parentid=b.dataid anda.SubType=206 and((a.Status=0) or (a.Status=1) or (a.Status=2) or(a.Status=3) or (a.Status=-1) or (a.Status=-2)) order by a.ownerID DESC,a.statusCan somebody help me?thorsten.[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: LiveReport - Task Comments and Instructionshttps://knowledge.opentext.com/knowledge/llisapi.dll/open/12384927Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Thorsten Schulte

Hi Lindsay,thanks for your quick answer.but when I change the datatyp to varchar2(30) for example, I got another error:[ORA-00932: inconsistent datatypes: expected NUMBER got LONG]Regardsthorsten.

Lindsay_Davies

Message from Lindsay Davies via eLink Hi Thorsten,String functions do not work well with Long datatypes.A quick search on Google shows this function you (or your DBA) couldcompile.http://www.oracle.com/technology/oramag/code/tips2003/052503.htmlThe example I have of how that can be used for Livelink long columnsis....SELECT dataid, extendeddata FROM dtree WHERE subtype = 223 ANDINSTR(long_to_char(rowid,'livelink9103','dtree','extendeddata'),CONCAT(CONCAT('submitMech',chr(39)),'=5'),1)<>0Good luck.Lindsay-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 06 December 2007 14:34To: eLink RecipientSubject: Hi Lindsay,Hi Lindsay,Posted by Schulte, Thorsten on 12/06/2007 09:31 AMHi Lindsay,thanks for your quick answer.but when I change the datatyp to varchar2(30) for example, I got anothererror:[ORA-00932: inconsistent datatypes: expected NUMBER got LONG]Regardsthorsten.[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: LiveReport - Task Comments and Instructionshttps://knowledge.opentext.com/knowledge/llisapi.dll/open/12384927Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/Livelink_LiveReports_DiscussionLivelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Thorsten Schulte

Hi Lindsay,thanks for your help, I get ahead with the function above.But now I have the Problem to get the Right Tasklist for a Task. It works only when there are no task groups.I tried it with ParentID, but when someone add a task group, the taskgroup has the parent ID and not the task List.Here is my SQL Statement:=====================================select a.name as Aufgabe, c.name as Aufgabenliste, a.datestarted as Startdatum, a.datedue as Faelligkeit,(CASE a.status When 0 Then 'Unerledigt' When 1 Then 'In Bearbeitung' When 2 Then 'Problematisch' When 3 Then 'Angehalten.' When -1 Then 'Abgeschlossen' When -2 Then 'Abgebrochen' Else 'Unknown' End) as Status, (CASE a.priority when 50 then 'Medium' when 100 then 'High' when 0 then 'Low' else 'none' END) as Prioritaet, kuaf.name as Zugewiesen,substr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'Instructions')+15,(instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'>')- instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'Instructions'))-16) as Anweisung,substr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'Comments')+11,(instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'Instruction')- instr(VIEGA.FN_CONV_LONG_TO_VARCHAR(a.rowid,'LL95SP1','dtree','extendedData'),'Comments'))-14) as Kommentar FROM DTree a left outer join Assignees on a.DataID=Assignees.DataID left outer join KUAF on Assignees.AssignedTo=KUAF.ID inner join Dtree c on a.parentid=c.dataid where a.subtype=206; =====================================greetings,thorsten.