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  |
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   |
rleishman
Messages: 3728 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 #320495 is a reply to message #320490] |
Thu, 15 May 2008 05:01   |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:54:53 CST 2025
|