create view error
Krishnakanth Vinnakota got error 9806 (duplicate/null field names in unnamed row types) creating a view over table(multiset(select ...)) joins, though the select ran fine. Art Kagel suggested a plain join with aliased columns. The poster found expressions without proper names caused the error and used OF TYPE, but then could not apply it because the column count varies. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
)