Unexpected weird result A+A' != U [message #665331] |
Wed, 30 August 2017 15:10 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
Hi
TB_view descrption.
Name Null Type
----------- ---- ------------
ACC_HEAD VARCHAR2(28)
TYPE CHAR(1)
AMOUNT NUMBER
DESTINATION CHAR(1)
it has total 23 rows
i am running a simple query against the view;
select * from tb_view where destination='E';
The result is (4 rows)
ACC_HEAD T AMOUNT D
---------------------------- - ---------- -
INVESTMENT C 0 E
OWNER EQUITY C -7.0E+06 E
SALES RETURN D 0 I
WITHDRAWS D 0 E
How come SALES RETURN is selected despite the fact it has "I" value on destination column.
SALES RETURN also shows when i re-run query with destination !='E' (gives me 20 rows)
Very strange and upset behavior.
Any tip?
|
|
|
|
Re: Unexpected weird result A+A' != U [message #665336 is a reply to message #665332] |
Thu, 31 August 2017 03:25 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
Made a little change in defining TB_VIEW and made the destination 'X' for sales return.
and the core part is
....
UNION
SELECT 'SALES RETURN', 'D', nvl(sum(NVL(sum(NVL(uprice,0)*NVL(QTY,0)),0) - (nvl(invcashz,0) +nvl(invcashz1,0) )),0), 'X'
from inv0p b, inv1p c
where b.invno=c.invno
and ccode not in (select ccode from cust where godown='Y')
and b.return2='Y'
group by invcashz, invcashz1
UNION
....
Here are few queries and result
SQL> select * from tb_view ;
ACC_HEAD T AMOUNT D
---------------------------- - ---------- -
ACCOUNT PAYABLE C 0 B
ACCOUNT RECEIVEABLE D 63615 B
BANK BALANCE D 0 B
BANK LOAN PAYABLE C 0 B
CASH IN HAND D 2140 B
DIRECT LABOR EXPENSE D 0 I
END_INVENTORY C 0 I
END_INVENTORY D 0 B
EXPENSES D 0 I
INVENTORY D 0 I
INVESTMENT C 0 E
MANF. OVERHEAD ACTUAL D 0 I
MANF. OVERHEAD APPLIED C 0 I
OTHER INCOME C 0 I
OWNER EQUITY C 2140 E
PURCHASE DISCOUNT C 0 I
PURCHASE RETURN C 63615 I
PURCHASES D 0 I
SALES C 0 I
SALES DISCOUNT D 0 I
SALES RETURN D 0 X
Under Applied/(Over Applied) C 0 I
WITHDRAWS D 0 E
23 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from tb_view where destination='E'
SQL> /
ACC_HEAD T AMOUNT D
---------------------------- - ---------- -
INVESTMENT C 0 E
OWNER EQUITY C 2140 E
SALES RETURN D 0 X
WITHDRAWS D 0 E
SQL> ed
Wrote file afiedt.buf
1* select * from tb_view where destination='X'
SQL> /
ACC_HEAD T AMOUNT D
---------------------------- - ---------- -
SALES RETURN D 0 X
@BlackSwan i can provide you the backup file (.dmp)
|
|
|
Re: Unexpected weird result A+A' != U [message #665338 is a reply to message #665336] |
Thu, 31 August 2017 04:29 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The full view definition along with the explain plan may help show what's gone wrong.
That said - this is absolutely a bug and you should really be talking to oracle support about getting it fixed.
|
|
|
|
|
Re: Unexpected weird result A+A' != U [message #665347 is a reply to message #665346] |
Thu, 31 August 2017 12:52 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
The result is corrected if i changed the code as follow (though this is not my desired output but i made it simplified to test/rectify)
...
UNION
SELECT 'SALES RETURN', 'D', NVL(sum(NVL(uprice,0)*NVL(QTY,0)),0) , 'I'
from inv0p b, inv1p c
where b.invno=c.invno
and ccode not in (select ccode from cust where godown='Y')
and b.return2='Y'
UNION
...
while original/problemtic code was
....
UNION
SELECT 'SALES RETURN', 'D', nvl(sum(NVL(sum(NVL(uprice,0)*NVL(QTY,0)),0) - (nvl(invcashz,0) +nvl(invcashz1,0) )),0), 'I'
from inv0p b, inv1p c
where b.invno=c.invno
and ccode not in (select ccode from cust where godown='Y')
and b.return2='Y'
group by invcashz, invcashz1
UNION
....
How can we compare both codes and predict possibility of error?
|
|
|
Re: Unexpected weird result A+A' != U [message #665354 is a reply to message #665347] |
Fri, 01 September 2017 02:24 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
>How can we compare both codes and predict possibility of error?
Providing a functioning test case so people can reproduce your issue rather than simply read your description of it is the only way.
|
|
|