Home » SQL & PL/SQL » SQL & PL/SQL » View and Table created from same query giving different result (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
View and Table created from same query giving different result [message #681041] Tue, 09 June 2020 03:25 Go to next message
s4.ora
Messages: 71
Registered: March 2010
Member
Hi

I have created a Table and a View from the same query, but upon executing a Query it is giving different results. What can be the possible reason...

Table Creation:

create table PATH_ICG_SCG_LL_DATA
as
with path_port_data as
(
	select *
	from 
	(
		select a.*
		,count(port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr) as PORT_COUNT
		,lag(a.port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr order by ELEMENT_ORDER) as A_PORT_ID
		,a.port_inst_id as Z_PORT_ID
		from 
		(
			select cpi.*, cpe.element_inst_id, cpe.port_inst_id
			,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by cpe.sequence) as ELEMENT_ORDER
			,count(1) over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr) as ELEMENT_COUNT
			from 
				(
					select * 
					from circ_path_inst@dbl.granite
					where circ_path_hum_id like 'LL%'
					and path_class = 'P'
				) cpi, 
				circ_path_element@dbl.granite cpe
			where cpi.circ_path_inst_id = cpe.circ_path_inst_id (+)
		) a
		where (ELEMENT_ORDER = 1 or ELEMENT_ORDER = ELEMENT_COUNT)
	)
	where ELEMENT_ORDER = ELEMENT_COUNT
)
select 
	ppd.*
	,aei.type as A_EQ_TYPE
	,zei.type as Z_EQ_TYPE
from 
	equip_inst@dbl.granite aei
	,epa@dbl.granite api
	,path_port_data ppd
	,epa@dbl.granite zpi
	,equip_inst@dbl.granite zei
where aei.equip_inst_id (+) = api.equip_inst_id
and api.port_inst_id (+) = ppd.A_PORT_ID
and ppd.Z_PORT_ID = zpi.port_inst_id (+)
and zpi.equip_inst_id = zei.equip_inst_id (+);
select * from PATH_ICG_SCG_LL_DATA where circ_path_inst_id = '131296';
View Creation:

create view PATH_ICG_SCG_LL_DATA
as
with path_port_data as
(
	select *
	from 
	(
		select a.*
		,count(port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr) as PORT_COUNT
		,lag(a.port_inst_id) over (partition by circ_path_inst_id, circ_path_rev_nbr order by ELEMENT_ORDER) as A_PORT_ID
		,a.port_inst_id as Z_PORT_ID
		from 
		(
			select cpi.*, cpe.element_inst_id, cpe.port_inst_id
			,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by cpe.sequence) as ELEMENT_ORDER
			,count(1) over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr) as ELEMENT_COUNT
			from 
				(
					select * 
					from circ_path_inst@dbl.granite
					where circ_path_hum_id like 'LL%'
					and path_class = 'P'
				) cpi, 
				circ_path_element@dbl.granite cpe
			where cpi.circ_path_inst_id = cpe.circ_path_inst_id (+)
		) a
		where (ELEMENT_ORDER = 1 or ELEMENT_ORDER = ELEMENT_COUNT)
	)
	where ELEMENT_ORDER = ELEMENT_COUNT
)
select 
	ppd.*
	,aei.type as A_EQ_TYPE
	,zei.type as Z_EQ_TYPE
from 
	equip_inst@dbl.granite aei
	,epa@dbl.granite api
	,path_port_data ppd
	,epa@dbl.granite zpi
	,equip_inst@dbl.granite zei
where aei.equip_inst_id (+) = api.equip_inst_id
and api.port_inst_id (+) = ppd.A_PORT_ID
and ppd.Z_PORT_ID = zpi.port_inst_id (+)
and zpi.equip_inst_id = zei.equip_inst_id (+);
select * from PATH_ICG_SCG_LL_DATA where circ_path_inst_id = '131296';
Re: View and Table created from same query giving different result [message #681042 is a reply to message #681041] Tue, 09 June 2020 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have created a Table and a View from the same query, but upon executing a Query it is giving different results. What can be the possible reason...
An Oracle optimizer bug.

Re: View and Table created from same query giving different result [message #681188 is a reply to message #681042] Wed, 24 June 2020 02:09 Go to previous messageGo to next message
s4.ora
Messages: 71
Registered: March 2010
Member
Thanks Michel...
Re: View and Table created from same query giving different result [message #681189 is a reply to message #681188] Wed, 24 June 2020 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you find the root of the problem?
If so what was it? How did you solve it?

Re: View and Table created from same query giving different result [message #681199 is a reply to message #681189] Thu, 25 June 2020 05:06 Go to previous message
s4.ora
Messages: 71
Registered: March 2010
Member
cpe.sequence was having duplicate values, I removed the one which were having duplicates, and executed the Table creation Statement.

,row_number() over (partition by cpi.circ_path_inst_id, cpi.circ_path_rev_nbr order by [b]cpe.sequence[/b]) as ELEMENT_ORDER
but for reason behind Table and View giving different results with the same query is still a mystery for me..
Previous Topic: Loading a CLOB with a KEY
Next Topic: ORA-00904: invalid identifier
Goto Forum:
  


Current Time: Mon Sep 28 11:44:06 CDT 2020