Home » Developer & Programmer » Reports & Discoverer » Same Code, Different Results, Reason? (Oracle Reports 6.08)
Same Code, Different Results, Reason? [message #553758] Tue, 08 May 2012 06:28 Go to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Hi,

I am making a report in which i have to calculate the value of ending inventory.

I wrote code in SQL and able to get perfect result. But when i copy/pasted the same code in "Oracle Reports 6", it has given me the different result which is wrong.

What are the possibilities? what am i missing in report?

Here is the code.

select sum((VALUE_OF_BAL_QTY_DATED(sum(qty), sale_type, sale_code, to_date('31012012', 'ddmmyyyy')  ) ))
  from
(Select sale_type, sale_code, qty
    from invp
    where invdate <=to_date('31012012', 'ddmmyyyy')
union all
Select sale_type, sale_code, qty*-1
    from invs
    where invdate <=to_date('31012012', 'ddmmyyyy') )
group by sale_type, sale_code



One more observation: I made a brand new report and copy/pasted the above code. And it was giving the perfect rquired result of 1.7 millions.

So could not figure out that what is going on? Is there any parameter which affects report'sparameter?????( i cant the question even as this had made me sick)
Re: Same Code, Different Results, Reason? [message #553761 is a reply to message #553758] Tue, 08 May 2012 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume that the date is the parameter.
Make the report display the parameter and check it has the value you think it has.
Re: Same Code, Different Results, Reason? [message #553764 is a reply to message #553761] Tue, 08 May 2012 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what is the datatype of the parameter?
Re: Same Code, Different Results, Reason? [message #553768 is a reply to message #553764] Tue, 08 May 2012 07:43 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Respected Cookiemonster,

I cannot understand your question. Can you rephrase your question after reading fllowing additional info.

When i defined function at Database level, the defination was
CREATE OR REPLACE FUNCTION VALUE_OF_BAL_QTY_DATED
  (pin_balqty        IN NUMBER,
   pin_sale_type       IN CHAR, 
   pin_sale_code	IN char,
   pin_dated	     IN DATE) RETURN number IS
.......
.......

The basic problem is why the same piece of code is giving different results in
1) in SQL >> perfect result
2) In new report >> perfect result
3) in old report (which is in transformation mode) >> bad result, shows purchases instead of ending invnetory value. I even used the new variable and seen its result with "srw.message"

But problem is still there.


Re: Same Code, Different Results, Reason? [message #553775 is a reply to message #553768] Tue, 08 May 2012 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt the problem is the function. I assume the problem is due to the select finding different records.
In the select you compare invdate to a value. In your example that value is hard-coded, but I would assume in the actual report you are using a parameter instead of the hard-coded value.
Is that correct?
If it is what is the datatype of that parameter (you can see the datatype in the parameters property pallete)?
Re: Same Code, Different Results, Reason? [message #553783 is a reply to message #553775] Tue, 08 May 2012 08:56 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Yes, you are right.
I am using a parameter (medate) in the actual situation. It has date type in declare section. This paramter, medate, is used many times in calculating many things as part of financial statements in the current report. Hence no problem is found in it except when calculating the ending inventory value as described above.

The function VALUE_OF_BAL_QTY_DATED is working perfectly in SQL environment and in NEW REPORT with to_date('31012012', 'ddmmyyyy') as input paramter of the function.
I also though that there might be a problem in medate variable so i replace it with to_date('31012012', 'ddmmyyyy'). Theroatically speaking, it must not have any difference. But in practical its giving false result.

Whereas if i start making new report is just like reinvneting the wheel for me.

The function is somewhat fine as giving me perfect result in SQL or in new report.

[Updated on: Tue, 08 May 2012 08:59]

Report message to a moderator

Re: Same Code, Different Results, Reason? [message #553784 is a reply to message #553783] Tue, 08 May 2012 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the datatype of medate?
Re: Same Code, Different Results, Reason? [message #553786 is a reply to message #553784] Tue, 08 May 2012 09:09 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Dear Sir,
Date type.

i told it in above reply

Quote:
It has date type in declare section
Re: Same Code, Different Results, Reason? [message #553796 is a reply to message #553786] Tue, 08 May 2012 09:36 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Add an item to the report to display the value of the date parameter. Check it's what you think it is.
If it's correct then there must be a bug in the function code, in which case you would have to share the code with us for further help.
Re: Same Code, Different Results, Reason? [message #553802 is a reply to message #553796] Tue, 08 May 2012 10:18 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
CREATE OR REPLACE FUNCTION VALUE_OF_BAL_QTY_DATED
  (pin_balqty        IN NUMBER,
   pin_sale_type       IN CHAR, 
   pin_sale_code	IN char,
   pin_dated	     IN DATE) RETURN number IS
   a NUMBER := 0;
   b NUMBER := pin_balqty;
  BEGIN
     b := pin_balqty;
     FOR j in (	Select *  
		FROM V0PROD
		where	sale_type=pin_sale_type and sale_code=pin_sale_code  
		and 	(invdate <=pin_dated or invdate is null)
		ORDER BY sale_type, sale_code,   decode(invdate, null, 0, 1) desc , INVDATE DESC, INVNO DESC ) LOOP
             IF b <= j.qty THEN
               a:= (b * j.uprice)+a;
               RETURN a;
             ELSE
               a:= (j.qty * j.uprice)+a;
               b:= b-j.qty;
             END IF;
     END LOOP;
     RETURN a;
  END;
/
Re: Same Code, Different Results, Reason? [message #553804 is a reply to message #553802] Tue, 08 May 2012 10:20 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
SQL> desc v0prod
Name
----------------
INVNO
SALE_TYPE
SALE_CODE
INVDATE
QTY
UPRICE
Re: Same Code, Different Results, Reason? [message #553810 is a reply to message #553804] Tue, 08 May 2012 10:36 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you check the parameter value like I told you to?

A desc of the table isn't much use without the datatypes.
Re: Same Code, Different Results, Reason? [message #553817 is a reply to message #553810] Tue, 08 May 2012 10:58 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
ohhh i found the solution!!!!!!!!!!!!!!!!!!!
reason: very weird.

wait let me compose the message.
Re: Same Code, Different Results, Reason? [message #553820 is a reply to message #553817] Tue, 08 May 2012 11:04 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
  select sum(VALUE_OF_BAL_QTY_DATED(sum(qty), sale_type, sale_code, medate))
  into einv
  from
 (Select sale_type, sale_code, nvl(qty,0) qty
     from invp
     where invdate <=medate
 union all
 Select sale_type, sale_code, nvl(qty,0)*-1
     from invs
     where invdate <=medate)
 group by sale_type, sale_code;


Adding NVL function has solved the problem.
and by the way there is no row selected in SQL with following code

Quote:
Select *
from invp
where qty is null;

Select *
from invs
where qty is null;


Dear cookiemonster, thank you for your support in my "sickness".

By the way what and why of this porblem and then the solution according to you?

Re: Same Code, Different Results, Reason? [message #553821 is a reply to message #553820] Tue, 08 May 2012 11:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't see any way those nvl's can make any difference unless there are rows where qty is null.
Do you have multiple copies of those tables in different schemas?
Are you sure you are checking the correct DB?

[Updated on: Tue, 08 May 2012 11:18]

Report message to a moderator

Re: Same Code, Different Results, Reason? [message #553826 is a reply to message #553821] Tue, 08 May 2012 11:46 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Do you have multiple copies of those tables in different schemas?
Basically invp and invs are views. And yes i have multiple copies of tables/views BUT UNDER DIFFERENT USERS.

Are you sure you are checking the correct DB?
DB connectivity is ok and confirmed as this problem has started 5 or 6 days before and was like a hot pill for me as for me the following example was perfect

SQL said 2+2=4
Reports said 2+2=5

So it was a horrible time for me.
But still what is the learning point for me? is NVL so important even if there is no null rows?
Re: Same Code, Different Results, Reason? [message #553835 is a reply to message #553826] Tue, 08 May 2012 13:04 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
nvl does nothing if there are no null values.
Anything else would be an oracle bug.

If nvl makes a difference then you must be getting null values in one of the places you run it.


Schema = user by the way.
Re: Same Code, Different Results, Reason? [message #553916 is a reply to message #553835] Wed, 09 May 2012 05:34 Go to previous messageGo to next message
bluetooth420
Messages: 141
Registered: November 2011
Senior Member
Thanks.
Few things are not in common practice.

Like record=tuple
Smile
Re: Same Code, Different Results, Reason? [message #553923 is a reply to message #553916] Wed, 09 May 2012 05:49 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Schema is all over the oracle documentation, tuple isn't.
Previous Topic: report output
Next Topic: How do I compare (lookup) values between worksheets
Goto Forum:
  


Current Time: Mon Jun 01 21:39:56 CDT 2020