LiveReport fetching incorrect results

Hi All,

We are running the following query:

"select s.value2, s1.value2, s.auditdate
from DAUDITNEW s , DAUDITNEW s1
where s.auditdate = (select max(d.auditdate) from DAUDITNEW d
where d.performerid =1000
and d.dataid=360552
and d.auditstr='AttrChange'
)
and s.auditdate=s1.auditdate
and s.valuekey='WF_ARA_CAT[1].Doc_Approval[1]'
and s1.valuekey='WF_ARA_CAT[1].Doc_Comment[1]' "

When run on SQLDeveloper the query fetches correct results as:

Yes Comment1 16thJune

But when we run the same query in a LiveReport, the following result is shown:

Comment1 Comment1 16thJune

Any pointers on this will be of great help.

Thanks,

Ketan

Find more posts tagged with

Comments

Colin_Schmidt

You have a DATAID being required in a Subquery looking for the latest date, why isn't the DATAID also used in the qualifications for the two DAUDITNEW references. They'll pull in every DATAID to search for the Date and VALUE1 & VALUE2.

select s.value2, s1.value2, s.auditdate

from DAUDITNEW s

join DAUDITNEW s1 on s1.dataid = 360552 and s.auditdate = s1.auditdate and s1.valuekey = 'WF_ARA_CAT[1].Doc_Comment[1]'

where s.dataid = 360552 and s.valuekey = 'WF_ARA_CAT[1].Doc_Approval[1]'

and s.auditdate = (select max(d.auditdate) from DAUDITNEW d where d.performerid = 1000 and d.dataid = 360552 and d.auditstr = 'AttrChange')

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Office)

+1 918.904 9223 (Cell)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Tue, Jun 16, 2015 at 8:41 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LiveReport fetching incorrect results Posted byketan.khurana@ontario.ca (Khurana, Ketan)On 06/16/2015 08:38 AM

Hi All,We are running the following query:"select s.value2, s1.value2, s.auditdate
from DAUDITNEW s , DAUDITNEW s1
where s.auditdate = (select max(d.auditdate) from DAUDITNEW d
where d.performerid =1000
and d.dataid=360552
and d.auditstr='AttrChange'
)
and s.auditdate=s1.auditdate
and s.valuekey='WF_ARA_CAT[1].Doc_Approval[1]'
and s1.valuekey='WF_ARA_CAT[1].Doc_Comment[1]' "When run on SQLDeveloper the query fetches correct results as:Yes Comment1 16thJuneBut when we run the same query in a LiveReport, the following result is shown:Comment1 Comment1 16thJune Any pointers on this will be of great help. Thanks,Ketan
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

sıɹɥɔ

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

Hi Ketan,

The reason you're seeing a difference between the results for SQLDeveloper and the results for LiveReports is that SQLDeveloper is presenting its result based on the position of the columns, whereas LiveReports is presenting results based on thenames of the columns. In your query you have the same name "value2" for both the first and the second column. As such LiveReports shows the same single value for both columns.

To fix this you'll want to provide different (alias) names for the columns. e.g.

select s.value2Approved, s1.value2 Comment, ...

(By the way, the reason that LiveReports uses names to refer to result columns is so that they can be referenced in the Display Columns part of a configuration.)

Hope this helps,

-chris

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, June 16, 2015 5:41 AM
To: eLink Recipient
Subject: LiveReport fetching incorrect results

LiveReport fetching incorrect results

Posted by ketan.khurana@ontario.ca (Khurana, Ketan) On 06/16/2015 08:38 AM

Hi All,We are running the following query:"select s.value2, s1.value2, s.auditdate
from DAUDITNEW s , DAUDITNEW s1
where s.auditdate = (select max(d.auditdate) from DAUDITNEW d
where d.performerid =1000
and d.dataid=360552
and d.auditstr='AttrChange'
)
and s.auditdate=s1.auditdate
and s.valuekey='WF_ARA_CAT[1].Doc_Approval[1]'
and s1.valuekey='WF_ARA_CAT[1].Doc_Comment[1]' "When run on SQLDeveloper the query fetches correct results as:Yes Comment1 16thJuneBut when we run the same query in a LiveReport, the following result is shown:Comment1 Comment1 16thJune Any pointers on this will be of great help. Thanks,Ketan

Ketan Khurana

Hi Chris,

I tried giving names to my columns in the following three ways:

1. select s.value2 Approval ,s1.value2 Comment ,s.auditdate AUDITDATE

2. select s.value2 "Approval" ,s1.value2 "Comment" ,s.auditdate "AUDITDATE"

3. select s.value2 AS "Approval" ,s1.value2 AS "Comment" ,s.auditdate AS "AUDITDATE"

Strangely enough, liveReport is throwing this error: "[This LiveReport does not allow Database modifications.]"

As soon as i remove the alias the old results are getting displayed. Could you tell what I may be doing wrong?

Thanks,

Mohita

sıɹɥɔ

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

Hi Mohita/Ketan,

Sorry, I chose a bad name for an alias: on an Oracle system the word "Comment" is one of the terms not allowed in a LiveReport. (This is to prevent DDL etc. from being executed.)

You'll want to choose a name different than "Comment". You merely need to distinguishs.value2 from s1.value2, with whatever name(s) are appropriate to you. (Even this: select s.value2 Approval, s1.value2, s.auditdate should work.)

Thanks,

-chris

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, June 16, 2015 8:51 PM
To: eLink Recipient
Subject: RE LiveReport fetching incorrect results

RE LiveReport fetching incorrect results

Posted by ketan.khurana@ontario.ca (Khurana, Ketan) On 06/16/2015 11:49 PM

Hi Chris,I tried giving names to my columns in the following three ways:1. select s.value2 Approval ,s1.value2 Comment ,s.auditdate AUDITDATE2. select s.value2 "Approval" ,s1.value2 "Comment" ,s.auditdate "AUDITDATE"3. select s.value2 AS "Approval" ,s1.value2 AS "Comment" ,s.auditdate AS "AUDITDATE"Strangely enough, liveReport is throwing this error: "[This LiveReport does not allow Database modifications.]"As soon as i remove the alias the old results are getting displayed. Could you tell what I may be doing wrong? Thanks,Mohita

Ketan Khurana

Hi Chris,

You are correct. I used a custom name and it started working absolutely fine.

Thanks a lot.

Isa_Hedemann