Home » SQL & PL/SQL » SQL & PL/SQL » Create view problem (sql,8.0.6.0.0,Xp)
Create view problem [message #320452] Thu, 15 May 2008 02:55 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I am stuck on create view statement ,i am trying to search it in forum but didnt find my required query/result.

I am trying to create view by using the following query in sql,the query is working fine in sql but when i am trying to create view by using this query then the error message display .

"ERROR at line 12:ORA-00942: table or view does not exist"

I am trying to create view by using the following query:
create view sr_ro_view
as 
SELECT 
	'R',A.SR_RO_NO,	A.SR_RO_DT,
	A.sr_ro_sts, F.sl_prod_cd,F.SL_PROD_NM,
	F.SL_FRN_CD,G.SL_FRN_NM,F.SL_PROD_NAT,F.SL_PROD_TYPE,
	A.SR_RTY_CD,SR_RTY_DESC,A.SR_RO_JTYPE,A.SR_VIN#,SL_ENG#,A.SR_REG_NO,
	A.SR_ITEM_CD,SL_ITEM_NM,A.SR_CUST_CD,E.SL_CUST_NM,
	null,
	to_number(null),
	null
FROM SR_RO_MST A,sales.sl_vin_mst B,sales.sl_item_mst C,
	sr_rtype_mst D,sales.sl_cust_mst E,sales.sl_prod_mst F,SALES.SL_FRANSH_MST G
WHERE A.SR_VIN#=B.SL_VIN#(+)
	AND F.SL_FRN_CD=G.SL_FRN_CD(+)
	AND substr(a.sr_item_cd,3,1)=to_char(F.sl_prod_cd(+))
	AND A.SR_ITEM_CD=C.SL_ITEM_CD(+)
	AND A.SR_RTY_CD=D.SR_RTY_CD(+)
	AND A.SR_CUST_CD=E.SL_CUST_CD(+)
union
--sublet
select 
	'S',a.SR_RO_NO,	to_date(null),
	null,to_number(null),null,
	to_number(null),null,null,
	null,null,null,
	null,null ,null,
	null,null,null,
	null,null,a.SR_SUBLET_CD,
	a.SR_SUBLET_AMT	,b.sr_sublet_desc 
	from SR_RO_SUBLET a,sr_sublet_mst b
	where a.SR_SUBLET_CD=b.SR_SUBLET_CD(+)
--lubricant
union
select 
	'L', a.sr_ro_no,to_date(null),
	null,to_number(null),null,
	to_number(null),null,null,
	null,null,null,
	null,null ,null,
	null,null,null,
	null,null,a.SR_POS_CD,
	a.SR_WRK_AMT,null
from  sr_ro_lub a
union
--parts
select 
	'P',a.SR_RO_NO,	to_date(null),
	null,to_number(null),null,
	to_number(null),null,null,
	null,null,null,
	null,null ,null,
	null,null,null,
	null,null,a.SR_PRT_CD,
	nvl(sr_prt_price,0) * nvl(sr_prt_qty,0) amt,
	b.sp_prt_desc 
from SR_RO_PART a,parts.sp_part_mst b
	where a.sr_prt_cd = b.sp_prt_cd(+)
/






Error is

"ERROR at line 12:ORA-00942: table or view does not exist"


In addition this query is working fine on sql if we are not using create view statement.
Re: Create view problem [message #320455 is a reply to message #320452] Thu, 15 May 2008 03:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It may be that you have privileges on this table via a ROLE. In order to create a view, privs must be granted DIRECTLY to you or to PUBLIC - not via a role.

Ross Leishman
Re: Create view problem [message #320463 is a reply to message #320455] Thu, 15 May 2008 03:31 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I have dba rights on my user
Re: Create view problem [message #320475 is a reply to message #320463] Thu, 15 May 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
see rleishman answer
In order to create a view, privs must be granted DIRECTLY to you or to PUBLIC

Roles are irrelevant in this case.

Regards
Michel
Re: Create view problem [message #320490 is a reply to message #320452] Thu, 15 May 2008 04:53 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

Still waiting for your advices.
Re: Create view problem [message #320495 is a reply to message #320490] Thu, 15 May 2008 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well pardon us, I'm sure.
I mean it's been nearly 2 hours since you posted, and you've only been told what the problem is twice.

We really must review the SLA terms. Lets check...
Ahh - there seems to have been a misunderstanding. You're on the 'Pay no money and get whatever help we feel like providing out of the goodness of our hearts' Service level.
Your SLA grants you the following rights of complaint : None
Your SLA provides the following arbitration procedures : None
You are guaranteed the following minimum response times: None
I hope this clears up your attitude problem.

Here's a summary of the most likely cause, as provided by @Rleishman:
It may be that you have privileges on this table via a ROLE. In order to create a view, privs must be granted DIRECTLY to you or to PUBLIC - not via a role.


In other words, check that the user that you are using has had SELECT privileges on the underlying tables explicitly granted to them.

DBA is a ROLE - role based privileges do not apply when creating Db objects.
Re: Create view problem [message #320502 is a reply to message #320452] Thu, 15 May 2008 05:27 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I am sorry if you feel that i have some attitute problem
Re: Create view problem [message #320514 is a reply to message #320502] Thu, 15 May 2008 05:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And I'm sorry that you are unable to admit that you have a problem.

Now, have you fixed the problem with your view?

If you don't know how to check which tables you have explicit grants on, look at the contents of the view USER_TAB_PRIVS, and see which tables are used in the view that you don't have privs on.
Re: Create view problem [message #320811 is a reply to message #320514] Fri, 16 May 2008 05:41 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks i have done by using column ilias
Previous Topic: performance of procedures
Next Topic: granting privlages to newly created user
Goto Forum:
  


Current Time: Tue Dec 06 02:16:44 CST 2016

Total time taken to generate the page: 0.17794 seconds