LiveReport shows wrong field values

Hello all,

I'm using the query shown below in a LiveReport.

select monthyear, source, von, zeitart, sum(ROUND(relevdauerstd)) "Summe_STD"
from DLZ_KENNZAHLEN@kennzahlen_lx03
where ersda between to_date(substr('01.12.2014 - 01.12.2014',1,10),'dd.mm.yyyy') and to_date(substr('01.12.2014 - 01.12.2014',14,10),'dd.mm.yyyy') and von <> nach
group by monthyear, source, von, zeitart
order by monthyear, source, von

Executing this LiveReport shows other results for field "Summe_STD" as when executing it from Toad IDE. The Select statement is exactely the same, as I used to copy and paste it from LiveReport editor to Toad. I've checked single records in the underlying Table and found, that Toad brings correct results, while values in LiveReport are wrong.

Content Server version is 10 Update 7 (version 10.0.0.2081)

Every hint is welcome!

Thanks in advance...

Regards,

Roland

Find more posts tagged with

Comments

Colin_Schmidt

Could the SQL engine under OT be calculating differently? Is the amount off decimals? Rounding? I get different results often from SQL Developer and a LiveReport, but it's a matter of rounding or how the LiveReports SQL engine (I'm guessing) does the work requested.

We also are using v10 Update 7.

Colin J.

On Fri, Jan 9, 2015 at 7:21 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LiveReport shows wrong field values Posted byrrottlaender@seepex.com (Rottlander, Roland)On 01/09/2015 07:17 AM

Hello all,I'm using the query shown below in a LiveReport.select monthyear, source, von, zeitart, sum(ROUND(relevdauerstd)) "Summe_STD"
from DLZ_KENNZAHLEN@kennzahlen_lx03
where ersda between to_date(substr('01.12.2014 - 01.12.2014',1,10),'dd.mm.yyyy') and to_date(substr('01.12.2014 - 01.12.2014',14,10),'dd.mm.yyyy') and von <> nach
group by monthyear, source, von, zeitart
order by monthyear, source, vonExecuting this LiveReport shows other results for field "Summe_STD" as when executing it from Toad IDE. The Select statement is exactely the same, as I used to copy and paste it from LiveReport editor to Toad. I've checked single records in the underlying Table and found, that Toad brings correct results, while values in LiveReport are wrong.Content Server version is 10 Update 7 (version 10.0.0.2081)Every hint is welcome!Thanks in advance...Regards,Roland
[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.

Heini

Hi Colin,

thanks for your reply.
I don't think that it is a matter of rounding, as the result set comming from the database is already rounded up to no-decimals.

Also, the difference between correct and wrong data is more than 15 - 20 %. So I don't think, rounding is the reason...

Best Regards,

Roland

John W. Simon, Jr.

"from DLZ_KENNZAHLEN@kennzahlen_lx03" - are you using a linked table?

Also, what user are you connected to the DB as (when running via TOAD)? Is this the same user that Content Server connects as?

Heini

Hello John,

sorry for my late reply - we've had an upgrade of a production server in the meatime and I couldn't spent to much time on this.
But you're right - I'm using a remote query via a database link.
When running this in TOAD, I'm connected to the original content server schema on the content server database.

Regards,
Roland

Capture.PNG.png

Heini

Hi All,

at least, I found the reason for this behavior:

The remote database was running with different NLS settings than the Livelink database.
Due to this reason, when computing date values, the first day of week called from LiveReport was Sunday, so I was getting
"2" for 1st of December 2014

But when running my query from SQLPLUS or Toad, first day of week was Monday, so I was getting "1".
As I used this expression in my WHERE clause, the whole result set was influenced.

I used an alternative function to get the day of the week (independent from NLS settings):

SELECT 1 + TRUNC(pDate) - TRUNC(pDate, 'IW') INTO vReturn FROM DUAL ;

pDate is the input as DATE parameter.

It's not my invention, but of some great guy on Oracle Technology Network...

Perhaps, this is useful for someone...

Regards,

Roland