create view error
9806 : Can not have duplicate/null field names in unnamed row types
my query is somthing like this
create view v_nm (a,b,....,q) as
select col1,col2 .......,col17 fromtable(multiset(select i1,i2,i3.. bla bla
)) as t1(c1,c2...)
left outer join
table(multiset(select ii1,ii2,ii3.. bla bla
)) as t2(c1,cc2...)
on (t1.c1=t2.c1)
to my surprise ***************************
select col1,col2 .......,col17 fromtable(multiset(select i1,i2,i3.. bla bla
)) as t1(c1,c2...)
left outer join
table(multiset(select ii1,ii2,ii3.. bla bla
)) as t2(c1,cc2...)
on (t1.c1=t2.c1)
is executing fine****************************
any idea
But WHY would you coerce a simple query into a table(multiset(select))? Try
this:
create view v_nm (a,b,....,q) as
select t1.i1 as col1, t1.i2 as col2, ..., t2.ii2 as col12 ...
from bla_table_1 as t1
left outer join bla_table_2 as t2
on t1.i1 = t2.ii1 ...;
Bet it works and runs faster to boot. K.I.S.S. is the watchword of the day!
Art S. Kagel
----- Original Message -----
From: Krishnakanth Vinnakota <ids@iiug.org>
At: 4/26 8:43:09
9806 : Can not have duplicate/null field names in unnamed row types
my query is somthing like this
create view v_nm (a,b,....,q) as
select col1,col2 .......,col17 fromtable(multiset(select i1,i2,i3.. bla bla
)) as t1(c1,c2...)
left outer join
table(multiset(select ii1,ii2,ii3.. bla bla
)) as t2(c1,cc2...)
on (t1.c1=t2.c1)
to my surprise ***************************
select col1,col2 .......,col17 fromtable(multiset(select i1,i2,i3.. bla bla
)) as t1(c1,c2...)
left outer join
table(multiset(select ii1,ii2,ii3.. bla bla
)) as t2(c1,cc2...)
on (t1.c1=t2.c1)
is executing fine****************************
any idea
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
EVERY select in the from class is a complex select from 5-6 tables with some case statements also
This error is because some columns are having expressions though i am
assigning a name its unable to identify proper datatype so its throwing error
so i used "of type" in the view creation. Now my problem is column number is
variable so i cannot define of type here.example
create view v_name
--cannot specify colum names here since t1 is a view whose column def/count
--is defined based on conditionsas
(
select t1.*,t2.col3,t3.col4,(NVL(t2.col2,'*') || '-' || NVL(t3.col2,'*')) as
rem_all from t1,t2,t3 where t1.col1=t2.col1 and t1.col1=t3.col1
)