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; } } }

Question Marks in the Live Report

Mamta_Rathi_(mrathi_(Delete)_2693719)

When a report is generated from Livelink, question marks are seen. This happens because the data for this field is blank for those records. Is there any way to replace the question marks by blank.Thank you.

Find more posts tagged with

Comments

Bhupinder_Singh

Message from Bhupinder Singh via eLinkI saw this posted once elsewhere in this discussion by a customer:The Oracle DECODE statement might be worth a try. To eliminate null valuesyou would include this in your decode statement.DECODE (null, ' ')MS SQL server uses a different statement to accomplish the same thing.- Bhupinder-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, June 10, 2003 12:53 AMTo: eLink RecipientSubject: Question Marks in the Live ReportQuestion Marks in the Live ReportPosted by Rathi, Mamta on 06/10/2003 12:40 AMWhen a report is generated from Livelink, question marks are seen. Thishappens because the data for this field is blank for those records. Is thereany way to replace the question marks by blank.Thank you.[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Mohsin Jessa via eLinkHi Mamta:If your d/b is an oracle d/b then the following may apply to your situation.In an oracle d/b a "?" OR an inverted "?" response for column data usuallymeans that your client interface is not capable of displaying the datastored in the database. This usually happens if the data stored in the d/bis a superset of the character set that your client interface can display(sql+, browser, etc...). If you think this is not a character set issue thenlet me know and I will see what I can do for you. In the mean time, can youalso confirm if you get the same results if you issue the same SQL from asql+ session (windows or dos version).If not, I'm sorry, I can't be of much help.Mohsin JessaOracle Technical Specialist.Customer Support - Escalation Group(519)888-7111 ext 2416-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, June 10, 2003 12:53 AMTo: eLink RecipientSubject: Question Marks in the Live ReportQuestion Marks in the Live ReportPosted by Rathi, Mamta on 06/10/2003 12:40 AMWhen a report is generated from Livelink, question marks are seen. Thishappens because the data for this field is blank for those records. Is thereany way to replace the question marks by blank.Thank you.[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Marie_Lindsay_(MLindsay_(Delete)_15608)

In SQL Server 7, you can use the syntax IsNull( column, 'replacementtext') to replace the null values. For example:SELECT dtree.name, ISNULL( dcomments, 'No description') FROM dtree...will list the contents of the dcomments field if there are any, and otherwise will display "No description".

Marie_Lindsay_(MLindsay_(Delete)_15608)

I found this info in online doc about Oracle 8i -- maybe someone else knows whether it's applicable to other versions as well:NVL Syntax PurposeIf expr1 is null, NVL returns expr2; if expr1 is not null, NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2. ExampleSELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE') "COMMISSION" FROM emp WHERE deptno = 30; ENAME COMMISSION---------- -------------------------------------ALLEN 300WARD 500MARTIN 1400BLAKE NOT APPLICABLETURNER 0JAMES NOT APPLICABLE

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

I tried using the NVL function on the WF_Value column of the WFComments workflow table and got an error "Illegal use of LONG datatype". We looked at the Oracle database and discovered that Nulls were not allowed in this column, which was a LONG datatype; so trying to select for Null will result in an error in situations like this. I don't know if it is possible to configure the database so that a LONG datatype will accept Nulls --has this been looked at by anyone at OT? We are running LL 9.1 on Oracle 8.1.7 in this instance.

eLink User

Message from Mohsin Jessa via eLinkHi Len:As you have observed, NVL function is not allowed against columns definedwith Long datatypes. It has nothing to do with storing null in the longcolumns. Nulls can be stored in long columns if it is defined to hold nulls.An example follows:SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle8i Enterprise Edition Release 8.1.7.0.0 - ProductionPL/SQL Release 8.1.7.0.0 - ProductionCORE 8.1.7.0.0 ProductionTNS for 32-bit Windows: Version 8.1.7.0.0 - ProductionNLSRTL Version 3.4.1.0.0 - ProductionSQL>SQL>SQL> create table l1 (c1 long);Table created.SQL> insert into l1 values ('this is a record on table l1 with a long columnc1');1 row created.SQL> insert into l1 values ('');1 row created.SQL> create table l2 (c1 long not null);Table created.SQL> insert into l2 values ('this is a record on table l2 with a long columnc1');1 row created.SQL> insert into l2 values ('');insert into l2 values ('') *ERROR at line 1:ORA-01400: cannot insert NULL into ("SCOTT"."L2"."C1")SQL> select rownum,c1 from l1; ROWNUM C1---------- -------------------------------------------------------------------------------- 1 this is a record on table l1 with a long column c1 2SQL> select rownum,c1 from l2; ROWNUM C1---------- -------------------------------------------------------------------------------- 1 this is a record on table l2 with a long column c1SQL> desc l1; Name Null?Type ----------------------------------------------------------------- -------- ------------------------ C1LONGSQL> desc l2 Name Null?Type ----------------------------------------------------------------- -------- ------------------------ C1 NOT NULLLONGSQL>Hope this clarified this for you. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, October 16, 2003 12:07 PMTo: eLink RecipientSubject: Using NVL function not allowed on LONG datatypeUsing NVL function not allowed on LONG datatypePosted by Olson, Len on 10/16/2003 12:04 PMI tried using the NVL function on the WF_Value column of the WFCommentsworkflow table and got an error "Illegal use of LONG datatype". We lookedat the Oracle database and discovered that Nulls were not allowed in thiscolumn, which was a LONG datatype; so trying to select for Null will resultin an error in situations like this.I don't know if it is possible to configure the database so that a LONGdatatype will accept Nulls --has this been looked at by anyone at OT? Weare running LL 9.1 on Oracle 8.1.7 in this instance.[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Question Marks in the Live Reporthttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3073776&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

Thanks.

Well, you have confirmed that the NVL function cannot be used with the WF_Value column in the WFComments table. But I still can't figure out how to show what I want in my report. I have a LiveReport that returns information on active workflow steps, and I want to include comments made by step performers in the comments tab. However, when I include the WF_Value in my select statement like this:

SELECT WF_Value "Comments" from WFComments, WSubWorkTask WHERE SubWorkTask_TaskID = WF_TaskID --(There are other tables & columns in the report, but this is how I was able to see comments on any steps that had them)--

All I get are steps where comments have been entered; I do not get any steps where no comments have been entered. Where the WF_Value column is empty, I would like to show something like 'No comments to date'. How can I do this? (I tried DECODE (WF_Value, *, '*', '', 'No Comments entered to date') "COMMENTS" but this results in an error too. )

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 12:32
To: eLink Recipient

RE Using NVL function not allowed on LONG datatype		Posted by eLink on 10/16/2003 12:32 PM
In reply to: Using NVL function not allowed on LONG datatype		Posted by RGSINC01Admin (Olson, Len) on 10/16/2003 12:04 PM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi Len:

As you have observed, NVL function is not allowed against columns defined
with Long datatypes. It has nothing to do with storing null in the long
columns. Nulls can be stored in long columns if it is defined to hold nulls.
An example follows:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>
SQL>
SQL> create table l1 (c1 long);

Table created.

SQL> insert into l1 values ('this is a record on table l1 with a long column
c1');

1 row created.

SQL> insert into l1 values ('');

1 row created.

SQL> create table l2 (c1 long not null);

Table created.

SQL> insert into l2 values ('this is a record on table l2 with a long column
c1');

1 row created.

SQL> insert into l2 values ('');
insert into l2 values ('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."L2"."C1")

SQL> select rownum,c1 from l1;

ROWNUM C1
---------- -----------------------------------------------------------------
---------------
1 this is a record on table l1 with a long column c1
2

SQL> select rownum,c1 from l2;

ROWNUM C1
---------- -----------------------------------------------------------------
---------------
1 this is a record on table l2 with a long column c1

SQL> desc l1;
Name Null?
Type
----------------------------------------------------------------- --------
------------------------
C1
LONG

SQL> desc l2
Name Null?
Type
----------------------------------------------------------------- --------
------------------------
C1 NOT NULL
LONG

SQL>

Hope this clarified this for you.

_____

Mohsin M. Jessa
Oracle Technical Specialist (Escalation Group)
Open Text Corporation
Ph: 519-888-7111 x 2416
Fax: 519-888-6737
Email: mjessa@opentext.com <mailto:mjessa@opentext.com>

_____

Join us in Orlando for LiveLinkUp 2003!

Open Text Conference
Orlando, Florida, USA
November 3-6, 2003

Find out how we're helping sixteen million great minds
work together to improve efficiencies and save money.

livelinkup-orlando.opentext.com <http://livelinkup-orlando.opentext.com>

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 12:07 PM
To: eLink Recipient
Subject: Using NVL function not allowed on LONG datatype

Using NVL function not allowed on LONG datatype
Posted by Olson, Len on 10/16/2003 12:04 PM

I tried using the NVL function on the WF_Value column of the WFComments
workflow table and got an error "Illegal use of LONG datatype". We looked
at the Oracle database and discovered that Nulls were not allowed in this
column, which was a LONG datatype; so trying to select for Null will result
in an error in situations like this.

I don't know if it is possible to configure the database so that a LONG
datatype will accept Nulls --has this been looked at by anyone at OT? We
are running LL 9.1 on Oracle 8.1.7 in this instance.

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

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

Topic: Question Marks in the Live Report
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3073776&
objAction=view

Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&
objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Mohsin Jessa via eLinkHi Len,You may try using the null and/or not null condition. An example...SQL> select rownum,c1,c2 from l1 where c1 is not null; ROWNUM C1 C2---------- -------------------- ---------- 1 this is a record on 123 table l1 with a long column c1SQL> select rownum,c1,c2 from l1 where c1 is null; ROWNUM C1 C2---------- -------------------- ---------- 1 234SQL> In short, you can't use any functions against columns with long datatypes. Hope this helps. _____ Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____ Join us in Orlando for LiveLinkUp 2003! Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great minds work together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, October 16, 2003 1:30 PMTo: eLink RecipientSubject: RE RE Using NVL function not allowed on LONG datatype

eLink User

Message from Mohsin Jessa via eLinkHi Len:I re-read your earlier posting. Are you telling me that your sql is notreturning rows that have null values in the WF_Value when you join thetables as in the following SQL:SELECT WF_Value "Comments" from WFComments, WSubWorkTask WHERESubWorkTask_TaskID = WF_TaskID;I don't have a problem:SQL> select l1.c1,l1.c2,l2.c2 from l1,l2 where l1.c2=l2.c2;C1 C2 C2-------------------- ---------- ----------this is a record on 123 123table l1 with a long column c1 123 123 234 234this is a second rec 234 234ord on table l1I found a way to get what you want, however it will not work fromLiveReports. From SQL+ there is no problem: Compare the following o/p withwhat I show above:SQL> col c1 null "No Comments";SQL> /C1 C2 C2-------------------- ---------- ----------this is a record on 123 123table l1 with a long column c1No Comments 123 123No Comments 234 234this is a second rec 234 234ord on table l1Hope this helps. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, October 16, 2003 3:47 PMTo: eLink RecipientSubject: RE RE RE Using NVL function not allowed on LONG datatypeRE RE RE Using NVL function not allowed on LONG datatypePosted by eLink on 10/16/2003 03:45 PMMessage from Mohsin Jessa via eLinkHi Len,You may try using the null and/or not null condition. An example...SQL> select rownum,c1,c2 from l1 where c1 is not null; ROWNUM C1 C2---------- -------------------- ---------- 1 this is a record on 123 table l1 with a long column c1SQL> select rownum,c1,c2 from l1 where c1 is null; ROWNUM C1 C2---------- -------------------- ---------- 1 234SQL>In short, you can't use any functions against columns with long datatypes.Hope this helps. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, October 16, 2003 1:30 PMTo: eLink RecipientSubject: RE RE Using NVL function not allowed on LONG datatype[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Question Marks in the Live Reporthttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3073776&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

Mohsin,

My SQL is not returning rows that have empty values (Null is not allowed in the column) -it only returns a row when a workflow comment has been stored in the record.

BTW, are you testing this on Livelink? I'm not able to follow your examples.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 4:13
To: eLink Recipient

RE RE RE RE Using NVL function not allowed on LONG datatype		Posted by eLink on 10/16/2003 04:12 PM
In reply to: RE RE RE Using NVL function not allowed on LONG datatype		Posted by eLink on 10/16/2003 03:45 PM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi Len:

I re-read your earlier posting. Are you telling me that your sql is not
returning rows that have null values in the WF_Value when you join the
tables as in the following SQL:

SELECT WF_Value "Comments" from WFComments, WSubWorkTask WHERE
SubWorkTask_TaskID = WF_TaskID;

I don't have a problem:

SQL> select l1.c1,l1.c2,l2.c2 from l1,l2 where l1.c2=l2.c2;

C1 C2 C2
-------------------- ---------- ----------
this is a record on 123 123
table l1 with a long
column c1

123 123
234 234
this is a second rec 234 234
ord on table l1

I found a way to get what you want, however it will not work from
LiveReports. From SQL+ there is no problem: Compare the following o/p with
what I show above:

SQL> col c1 null "No Comments";
SQL> /

C1 C2 C2
-------------------- ---------- ----------
this is a record on 123 123
table l1 with a long
column c1

No Comments 123 123
No Comments 234 234
this is a second rec 234 234
ord on table l1

Hope this helps.

_____

Mohsin M. Jessa
Oracle Technical Specialist (Escalation Group)
Open Text Corporation
Ph: 519-888-7111 x 2416
Fax: 519-888-6737
Email: mjessa@opentext.com <mailto:mjessa@opentext.com>

_____

Join us in Orlando for LiveLinkUp 2003!

Open Text Conference
Orlando, Florida, USA
November 3-6, 2003

Find out how we're helping sixteen million great minds
work together to improve efficiencies and save money.

livelinkup-orlando.opentext.com <http://livelinkup-orlando.opentext.com>

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 3:47 PM
To: eLink Recipient
Subject: RE RE RE Using NVL function not allowed on LONG datatype

RE RE RE Using NVL function not allowed on LONG datatype
Posted by eLink on 10/16/2003 03:45 PM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi Len,

You may try using the null and/or not null condition. An example...

SQL> select rownum,c1,c2 from l1 where c1 is not null;

ROWNUM C1 C2
---------- -------------------- ----------
1 this is a record on 123
table l1 with a long
column c1

SQL> select rownum,c1,c2 from l1 where c1 is null;

ROWNUM C1 C2
---------- -------------------- ----------
1 234

SQL>

In short, you can't use any functions against columns with long datatypes.

Hope this helps.

_____

Mohsin M. Jessa
Oracle Technical Specialist (Escalation Group)
Open Text Corporation
Ph: 519-888-7111 x 2416
Fax: 519-888-6737
Email: mjessa@opentext.com <mailto:mjessa@opentext.com>

_____

Join us in Orlando for LiveLinkUp 2003!

Open Text Conference
Orlando, Florida, USA
November 3-6, 2003

Find out how we're helping sixteen million great minds
work together to improve efficiencies and save money.

livelinkup-orlando.opentext.com <http://livelinkup-orlando.opentext.com>

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 1:30 PM
To: eLink Recipient
Subject: RE RE Using NVL function not allowed on LONG datatype

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

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

Topic: Question Marks in the Live Report
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3073776&
objAction=view

Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&
objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Mohsin Jessa via eLinkHi Len:Give me your complete SQL statement and I'll see what I can do.My examples were purely at the d/b level. I always try to do things first atthe d/b level especially if I am debugging / optimizing the SQL statement.What I don't understand is if your column is defined as not null then whyare you trying to test it with NVL function? and Why are you expecting tosee records with "no comments".I checked the default LiveLink schema definition for the WFComments table,the WF_Value column is a "null able" column. So have you changed your schemadefinition ?May be a LiveReport specialist can jump in. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, October 16, 2003 4:23 PMTo: eLink RecipientSubject: RE RE RE RE RE Using NVL function not allowed on LONG datatype

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

Hohsin,

Thanks for the help. I was trying to use the NVL function on the WF_Value column before I learned that its configuration would not accept a Null. One of our sys admins showed me the configuration on our test system. But looking at the schema reference document the Null Status for all the fields in WFComments is shown as Not Null, except for WF_Value which is listed as Null in Null Status.

Perhaps the Null Status configuration of our database for this table is reversed from the default definition. I will look into it with our sys admin.

My current report filters out records that don't have any comments, and that is the condition I am trying to resolve --I want records with and without comments. So far, you have helped me understand why the NVL function was getting me nowhere. (I am not a db admin, and I have a lot to learn about SQL) But I'm still hoping that I can get to a report that will show comments when comments exist, and allow me to insert 'No comments entered to date' where comments don't exist.

SQL for my LiveReport is below. Perhaps you can see what can be done with it to return the records that don't have comments.

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

SELECT

subwork_title "TITLE",

TO_CHAR(work_dateinitiated , 'MM/DD/YYYY') "INITIATED",

subworktask_title "TASK NAME",

TO_CHAR(subworktask_dateready, 'MM/DD/YYYY') "DATE RECEIVED",

TO_CHAR(subworktask_DateDue_Max, 'MM/DD/YYYY') "DUE DATE",

DECODE ((SELECT '*' FROM WSubWorkTask w1 WHERE WSubWorkTask.SubWorkTask_DateDue_Max < SYSDATE AND w1.SubWorkTask_TaskID = WSubWorkTask.SubWorkTask_TaskID AND w1.SubWorkTask_WorkID = WSubWorkTask.SubWorkTask_WorkID), '*', 'Overdue', NULL, 'No') AS "LATE",

DECODE (kuaf.type, 0, kuaf.lastname || ', ' || kuaf.firstname, 1, kuaf.name) "PERFORMER NAME",

DECODE (work_status,2,'Executing',1,'Suspended',-2,'Stopped') "WORKFLOW STATUS",

DECODE (subworktask_status,-1,'Done',2,'Ready',3,'Started',4,'Suspended',5,'Exe
cuting') || ' ' || TO_CHAR(subworktask_DateDone, 'MM/DD/YYYY') "TASK STATUS",

WF_Value "COMMENTS"

FROM

wwork, kuaf, wsubworktask, wworkaudit, wsubwork, dtree, wmap, WFComments

WHERE

dtree.dataid=wmap.map_mapobjid

AND subwork_mapid=map_mapid

AND kuaf.id=subworktask_performerid

AND Work_workid=%1

AND work_workid=subwork_workid

AND work_workid=workaudit_workid

AND work_workid=subworktask_workid

AND workaudit_status=1

AND SubWorkTask_TaskID = WF_TaskID

AND WF_WorkflowID = SubWork_SubWorkID

AND kuaf.id=subworktask_performerid

AND NOT work_status<=-1

AND NOT subworktask_status=1

AND NOT subworktask_status<=-2

ORDER BY 'DATE RECEIVED'

%1 is user input - ID for an executing workflow

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 5:09
To: eLink Recipient

RE RE RE RE RE RE Using NVL function not allowed on LONG datatype		Posted by eLink on 10/16/2003 05:09 PM
In reply to: RE RE RE RE RE Using NVL function not allowed on LONG datatype		Posted by RGSINC01Admin (Olson, Len) on 10/16/2003 04:23 PM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi Len:

Give me your complete SQL statement and I'll see what I can do.

My examples were purely at the d/b level. I always try to do things first at
the d/b level especially if I am debugging / optimizing the SQL statement.

What I don't understand is if your column is defined as not null then why
are you trying to test it with NVL function? and Why are you expecting to
see records with "no comments".

I checked the default LiveLink schema definition for the WFComments table,
the WF_Value column is a "null able" column. So have you changed your schema
definition ?

May be a LiveReport specialist can jump in.

_____

Mohsin M. Jessa
Oracle Technical Specialist (Escalation Group)
Open Text Corporation
Ph: 519-888-7111 x 2416
Fax: 519-888-6737
Email: mjessa@opentext.com <mailto:mjessa@opentext.com>

_____

Join us in Orlando for LiveLinkUp 2003!

Open Text Conference
Orlando, Florida, USA
November 3-6, 2003

Find out how we're helping sixteen million great minds
work together to improve efficiencies and save money.

livelinkup-orlando.opentext.com <http://livelinkup-orlando.opentext.com>

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 16, 2003 4:23 PM
To: eLink Recipient
Subject: RE RE RE RE RE Using NVL function not allowed on LONG datatype

eLink User

Message from Mohsin Jessa via eLinkHi Len:Can we first confirm if there are any records in the table (i.e WorkFlows)with null comments ?You can do a quick test on your d/b with the following sql statements:select count(*) as "# of Rec without Comments" from WFComments whereWF_Value is null;select count(*) as "# of Rec with Comments" from WFComments where WF_Valueis not null;select count(*) as "Total records" from WFComments;If the first sql statement returns a value then I'd be concerned, as thatwill confirm your concern that your livereport is not returning records withno comments.If you are not comfortable with SQL+ then you should be able to run theabove statements from separate livereports (one sql per livereport) or youcan ask your sys admin or dba for help.Once again, from your table definition of WFComments, where the WF_Typecolumns is defined as a NOT NULL column, all your workflows WILL havecomments. The d/b will reject any attempts to insert or update a table suchthat the WF_Type column will result in a null value. If this has been theway your LiveLink had been configured there is no way you will have anyworkkflow without comments.If the WF_Type column was changed mid-stream (i.e after there was data inthe WFComments table) from Null to Not Null, all attempts would have failedas Oracle checks the contents of the table to ensure that the new Not Nullconstraint is not violated (there by ensuring that there are no workflowswithout comments).Hope this helps. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, October 17, 2003 7:56 AMTo: eLink RecipientSubject: RE RE RE RE RE RE RE Using NVL function not allowed on LONGdatatype

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

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

Hi. I've been checking with sys admin and a database admin and have learned that our db is configured correctly to match the standard Livelink schema, so the WF_Value column will accept nulls. We do have plenty of workflows with no comments. The sticking point is the LONG datatype --it will not accept a NVL function, and DECODE doesn't seem to work either. Do you know if CASE can be used with a LONG datatype? We have another instance running on Oracle 9, which accepts CASE functions so that might be worth testing.

I'll look at it again on Monday. Thanks for now!

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, October 17, 2003 3:57
To: eLink Recipient

RE RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype		Posted by eLink on 10/17/2003 03:57 PM
In reply to: RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype		Posted by RGSINC01Admin (Olson, Len) on 10/17/2003 07:55 AM

Message from Mohsin Jessa <mjessa@opentext.com> via eLink

Hi Len:

Can we first confirm if there are any records in the table (i.e WorkFlows)
with null comments ?

You can do a quick test on your d/b with the following sql statements:

select count(*) as "# of Rec without Comments" from WFComments where
WF_Value is null;

select count(*) as "# of Rec with Comments" from WFComments where WF_Value
is not null;

select count(*) as "Total records" from WFComments;

If the first sql statement returns a value then I'd be concerned, as that
will confirm your concern that your livereport is not returning records with
no comments.

If you are not comfortable with SQL+ then you should be able to run the
above statements from separate livereports (one sql per livereport) or you
can ask your sys admin or dba for help.

Once again, from your table definition of WFComments, where the WF_Type
columns is defined as a NOT NULL column, all your workflows WILL have
comments. The d/b will reject any attempts to insert or update a table such
that the WF_Type column will result in a null value. If this has been the
way your LiveLink had been configured there is no way you will have any
workkflow without comments.

If the WF_Type column was changed mid-stream (i.e after there was data in
the WFComments table) from Null to Not Null, all attempts would have failed
as Oracle checks the contents of the table to ensure that the new Not Null
constraint is not violated (there by ensuring that there are no workflows
without comments).

Hope this helps.

_____

Mohsin M. Jessa
Oracle Technical Specialist (Escalation Group)
Open Text Corporation
Ph: 519-888-7111 x 2416
Fax: 519-888-6737
Email: mjessa@opentext.com <mailto:mjessa@opentext.com>

_____

Join us in Orlando for LiveLinkUp 2003!

Open Text Conference
Orlando, Florida, USA
November 3-6, 2003

Find out how we're helping sixteen million great minds
work together to improve efficiencies and save money.

livelinkup-orlando.opentext.com <http://livelinkup-orlando.opentext.com>

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, October 17, 2003 7:56 AM
To: eLink Recipient
Subject: RE RE RE RE RE RE RE Using NVL function not allowed on LONG
datatype

eLink User

Message from Mohsin Jessa via eLinkHi Len:Unfortunately, the case statement too will not work against LONG columns.You can't even reference a long column in a trigger on any inserts orupdates to the table, to replace the null values for WFType column with a"No Comments" literal. You'll get the error: ORA-04093: references tocolumns of type LONG are not allowed in triggers.Do you absolutely have to replace a blank/null comment field with a "NoComments" literal/string ? I'd say in a report "white space" sometimes doeslook better than the "cluttered" look you'll get if all records have sometext.For example:SQL> select name,nvl(title,'No Title') from kuaf where rownum < 10;NAMENVL(TITLE,'NOTITLE')---------------------------------------------------------------- -----------------------------------Admin No TitleDefaultGroup No Titlejason (Delete) 2034 InternetArchitectpeter (Delete) 2322 INET MgrCoordinators No TitleMembers No TitleGuests No TitleCoordinators No TitleMembers No Title9 rows selected.SQL> select name,title from kuaf where rownum < 10;NAME TITLE---------------------------------------------------------------- -----------------------------------AdminDefaultGroupjason (Delete) 2034 InternetArchitectpeter (Delete) 2322 INET MgrCoordinatorsMembersGuestsCoordinatorsMembers9 rows selected.I personally would prefer the second output.Hope this helps. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, October 17, 2003 4:33 PMTo: eLink RecipientSubject: RE RE RE RE RE RE RE RE RE Using NVL function not allowed onLONG datatype

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard via eLinkMohsin, Thanks for the info regarding the CASE statement. I don't HAVE to have the 'No comments to date' output in the report --that is only intended to clarify for the user where comments have not been made. Blank space would be preferable to not showing the records at all. Now, how do I get the report to return the empty records? -----Original Message----- From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Mon 10/20/2003 10:25 AM To: eLink Recipient Cc: Subject: RE RE RE RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype RE RE RE RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype Posted by eLink on 10/20/2003 10:19 AM In reply to: RE RE RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype Posted by RGSINC01Admin (Olson, Len) on 10/17/2003 04:33 PM Message from Mohsin Jessa via eLinkHi Len: Unfortunately, the case statement too will not work against LONG columns. You can't even reference a long column in a trigger on any inserts or updates to the table, to replace the null values for WFType column with a "No Comments" literal. You'll get the error: ORA-04093: references to columns of type LONG are not allowed in triggers. Do you absolutely have to replace a blank/null comment field with a "No Comments" literal/string ? I'd say in a report "white space" sometimes does look better than the "cluttered" look you'll get if all records have some text. For example: SQL> select name,nvl(title,'No Title') from kuaf where rownum < 10; NAME NVL(TITLE,'NOTITLE') ---------------------------------------------------------------- ----------- ------------------------ Admin No Title DefaultGroup No Title jason (Delete) 2034 Internet Architect peter (Delete) 2322 INET Mgr Coordinators No Title Members No Title Guests No Title Coordinators No Title Members No Title 9 rows selected. SQL> select name,title from kuaf where rownum < 10; NAME TITLE ---------------------------------------------------------------- ----------- ------------------------ Admin DefaultGroup jason (Delete) 2034 Internet Architect peter (Delete) 2322 INET Mgr Coordinators Members Guests Coordinators Members 9 rows selected. I personally would prefer the second output. Hope this helps. _____ Mohsin M. Jessa Oracle Technical Specialist (Escalation Group) Open Text Corporation Ph: 519-888-7111 x 2416 Fax: 519-888-6737 Email: mjessa@opentext.com _____ Join us in Orlando for LiveLinkUp 2003! Open Text Conference Orlando, Florida, USA November 3-6, 2003 Find out how we're helping sixteen million great minds work together to improve efficiencies and save money. livelinkup-orlando.opentext.com -----Original Message----- From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, October 17, 2003 4:33 PM To: eLink Recipient Subject: RE RE RE RE RE RE RE RE RE Using NVL function not allowed on LONG datatype _____ [To reply to this thread, use your normal e-mail reply function.] Topic: Question Marks in the Live Report Discussion: Livelink LiveReports Discussion Livelink Server: Knowledge Center

eLink User

Message from Mohsin Jessa via eLinkHi Len:It would be very hard for me to debug your SQL without the data. You may tryopening a ticket for that and ask for a LiveReports specialist. This wouldnot be an appropriate/efficient forum for that.My suggestion would be to debug your where clause. That is where records areselected and rejected.I'm hoping some LiveReport/schema expert may have been following thisdiscussion and that he will inject his expertise at this point.The trick is to look at the following conditions which would affect theWFComments table:AND SubWorkTask_TaskID = WF_TaskIDAND WF_WorkflowID = SubWork_SubWorkIDI'm not sure if the LiveLink permissions come into play here. This is alsowhy to get the SQL working first, I test it outside LL environment.Hope this helps. _____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Monday, October 20, 2003 11:39 AMTo: eLink RecipientSubject: RE RE RE RE RE RE RE RE RE RE RE Using NVL function not allowedon LONG datatypeRE RE RE RE RE RE RE RE RE RE RE Using NVL function not allowedon LONG datatypePosted by Olson, Len on 10/20/2003 11:39 AMMessage from Olson, Leonard via eLinkMohsin,Thanks for the info regarding the CASE statement. I don't HAVE to have the'No comments to date' output in the report --that is only intended toclarify for the user where comments have not been made. Blank space wouldbe preferable to not showing the records at all. Now, how do I get thereport to return the empty records? -----Original Message----- From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Mon 10/20/2003 10:25 AM To: eLink Recipient Cc: Subject: RE RE RE RE RE RE RE RE RE RE Using NVL function not allowed onLONG datatypeRE RE RE RE RE RE RE RE RE RE Using NVL function not allowed on LONGdatatype Posted by eLink on 10/20/2003 10:19 AMIn reply to: RE RE RE RE RE RE RE RE RE Using NVL function not allowed onLONG datatype Posted by RGSINC01Admin (Olson, Len) on 10/17/2003 04:33 PMMessage from Mohsin Jessa via eLinkHi Len:Unfortunately, the case statement too will not work against LONG columns.You can't even reference a long column in a trigger on any inserts orupdates to the table, to replace the null values for WFType column with a"No Comments" literal. You'll get the error: ORA-04093: references tocolumns of type LONG are not allowed in triggers.Do you absolutely have to replace a blank/null comment field with a "NoComments" literal/string ? I'd say in a report "white space" sometimes doeslook better than the "cluttered" look you'll get if all records have sometext.For example:SQL> select name,nvl(title,'No Title') from kuaf where rownum < 10;NAMENVL(TITLE,'NOTITLE')---------------------------------------------------------------- -----------------------------------Admin No TitleDefaultGroup No Titlejason (Delete) 2034 InternetArchitectpeter (Delete) 2322 INET MgrCoordinators No TitleMembers No TitleGuests No TitleCoordinators No TitleMembers No Title9 rows selected.SQL> select name,title from kuaf where rownum < 10;NAME TITLE---------------------------------------------------------------- -----------------------------------AdminDefaultGroupjason (Delete) 2034 InternetArchitectpeter (Delete) 2322 INET MgrCoordinatorsMembersGuestsCoordinatorsMembers9 rows selected.I personally would prefer the second output.Hope this helps._____Mohsin M. JessaOracle Technical Specialist (Escalation Group)Open Text CorporationPh: 519-888-7111 x 2416Fax: 519-888-6737Email: mjessa@opentext.com _____Join us in Orlando for LiveLinkUp 2003!Open Text ConferenceOrlando, Florida, USANovember 3-6, 2003Find out how we're helping sixteen million great mindswork together to improve efficiencies and save money.livelinkup-orlando.opentext.com -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, October 17, 2003 4:33 PMTo: eLink RecipientSubject: RE RE RE RE RE RE RE RE RE Using NVL function not allowed onLONG datatype _____[To reply to this thread, use your normal e-mail reply function.]Topic: Question Marks in the Live ReportDiscussion: Livelink LiveReports DiscussionLivelink Server: Knowledge Center[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Question Marks in the Live Reporthttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3073776&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe