Home » SQL & PL/SQL » SQL & PL/SQL » Unexpected weird result A+A' != U (10.2.0.1.0)
Unexpected weird result A+A' != U [message #665331] Wed, 30 August 2017 15:10 Go to next message
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 #665332 is a reply to message #665331] Wed, 30 August 2017 15:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any tip?
Since you did not provide sufficient detail for us to reproduce what you claim, your only recourse is to submit Bug Report to MOS.

Either Oracle is correct & you are mistaken or you are correct & Oracle has a bug that needs to be fixed.
Re: Unexpected weird result A+A' != U [message #665336 is a reply to message #665332] Thu, 31 August 2017 03:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #665342 is a reply to message #665338] Thu, 31 August 2017 07:16 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
does the error go away if you change your CHAR(1) definitions to VARCHAR(1)?
Re: Unexpected weird result A+A' != U [message #665346 is a reply to message #665342] Thu, 31 August 2017 12:45 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Bill B wrote on Thu, 31 August 2017 17:16
does the error go away if you change your CHAR(1) definitions to VARCHAR(1)?
Its a view and thus has made its type itself as my view starts as follows:

CREATE OR REPLACE VIEW TB_VIEW (ACC_HEAD, TYPE, AMOUNT, DESTINATION)
AS
SELECT 'INVENTORY', 		'D', NVL(SUM(OST*OP),0)	, 'I'	FROM PROD
UNION
...
Re: Unexpected weird result A+A' != U [message #665347 is a reply to message #665346] Thu, 31 August 2017 12:52 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: How to show multiple data in single row
Next Topic: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g
Goto Forum:
  


Current Time: Fri Mar 29 01:18:53 CDT 2024