What if the subquery returns multiple records?
Pete Krueger, August 16, 2002 - 3:24 pm UTC
SELECT *
FROM TABLE(SELECT m.md_ntab
FROM metadata_tab m, TABLE(m.md_ntab) n
WHERE m.key_context = 'PRI' AND
m.grouping = 'Element Metadata' AND
n.name = 'PUB_CODE' AND
n.value = 'FLLB') t
WHERE t.name = 'volume.break'
SQL> /
SELECT * FROM TABLE(SELECT m.md_ntab
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
I tried changing:
WHERE t.name = 'volume.break'
TO
WHERE 'volume.break' IN t.name
But I get the same error - any suggestions?
Thanks a ton!
-Pete
August 16, 2002 - 4:03 pm UTC
You mean like this then:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y myTableType )
2 nested table y store as y_tab
3 /
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index y_idx on y_tab(nm,vl)
2 /
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values
2 ( 1, myTableType( myScalarType( 'volume.break', '1' ),
3 myScalarType( 'ELEMENT_NAME', 'topic'),
4 myScalarType( 'PUB_CODE', 'FLLB' )
5 ) );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values
2 ( 2, myTableType( myScalarType( 'volume.break', '2' ),
3 myScalarType( 'ELEMENT_NAME', 'xtopic'),
4 myScalarType( 'PUB_CODE', 'FLLB' )
5 ) );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select t.x, t2.nm, t2.vl
2 from T, TABLE(t.y) T2
3 where t.x in ( select t.x
4 from t, TABLE(t.y) t2
5 where t.x in ( 1, 2 )
6 and t2.nm = 'PUB_CODE'
7 and t2.vl = 'FLLB' )
8 and nm = 'volume.break'
9 /
X NM VL
---------- -------------------- --------------------
1 volume.break 1
2 volume.break 2
I'm experimenting on it now but using your example...
Pete Krueger, August 16, 2002 - 4:43 pm UTC
Using your example, I would just add this (SEGMENT_UUID):
insert into t values
( 1, myTableType( myScalarType( 'volume.break', '1' ),
myScalarType( 'ELEMENT_NAME', 'topic'),
myScalarType( 'SEGMENT_UUID', 'I123'),
myScalarType( 'PUB_CODE', 'FLLB' )
) );
1 row created.
insert into t values
( 2, myTableType( myScalarType( 'volume.break', '2' ),
myScalarType( 'ELEMENT_NAME', 'xtopic'),
myScalarType( 'SEGMENT_UUID', 'I124'),
myScalarType( 'PUB_CODE', 'FLLB' )
) );
And the results I'm working for are:
VL VL
-------------------- --------------------
1 I123
2 I124
Alternatively, I could make do with:
NM VL
-------------------- --------------------
volume.break 1
SEGMENT_UUID I123
volume.break 2
SEGMENT_UUID I124
But maybe a join can be done somehow on the values.
I'll keep testing! Thanks!
-Pete
August 16, 2002 - 6:51 pm UTC
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values
2 ( 1, myTableType( myScalarType( 'volume.break', '1' ),
3 myScalarType( 'SEGMENT_UUID', 'I123'),
4 myScalarType( 'ELEMENT_NAME', 'topic'),
5 myScalarType( 'PUB_CODE', 'FLLB' )
6 ) );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values
2 ( 2, myTableType( myScalarType( 'volume.break', '2' ),
3 myScalarType( 'SEGMENT_UUID', 'I124'),
4 myScalarType( 'ELEMENT_NAME', 'xtopic'),
5 myScalarType( 'PUB_CODE', 'FLLB' )
6 ) );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select t.x,
2 max( decode( t2.nm, 'volume.break', t2.vl, null )) "volumn.break",
3 max( decode( t2.nm, 'SEGMENT_UUID', t2.vl, null )) SEGMENT_UUID
4 from T, TABLE(t.y) T2
5 where t.x in ( select t.x
6 from t, TABLE(t.y) t2
7 where t.x in ( 1, 2 )
8 and t2.nm = 'PUB_CODE'
9 and t2.vl = 'FLLB' )
10 and nm in ('volume.break','SEGMENT_UUID')
11 group by t.x
12 /
X volumn.break SEGMENT_UUID
---------- -------------------- --------------------
1 1 I123
2 2 I124
ops$tkyte@ORA817DEV.US.ORACLE.COM>
QED
It's close...probably good enough...but not perfect
Pete Krueger, August 19, 2002 - 2:59 pm UTC
It returns 138 rows instead of 14 that I'd like - the 124 unwanted records just have an empty volume number (I could probably make due with this and in java just say if the volume number is null or an empty string, go to the next record).
It returns all FLLB records because all records will have a SEGMENT_UUID, but only some will have volume.break.
Instead of:
name IN ('volume.break', 'SEGMENT_UUID')
I need: 'If I have a volume.break, include the SEGMENT_UUID...else skip the record'
At least I don't have to put the Oracle objects into Java structs and loop through each objects' attributes - that helps a lot.
If you have any last advice/suggestions, that'd be great!
Thanks a lot Tom - it's been a good learning experience and I look forward to following your future discussions.
Pete
August 19, 2002 - 4:00 pm UTC
select * from
(
select t.x,
max( decode( t2.nm, 'volume.break', t2.vl, null )) "volumn.break",
max( decode( t2.nm, 'SEGMENT_UUID', t2.vl, null )) SEGMENT_UUID
from T, TABLE(t.y) T2
where t.x in ( select t.x
from t, TABLE(t.y) t2
where t.x in ( 1, 2 )
and t2.nm = 'PUB_CODE'
and t2.vl = 'FLLB' )
and nm in ('volume.break','SEGMENT_UUID')
group by t.x
)
where "volumne.break" IS NOT NULL
/
QED*2
what if...
dwl, February 17, 2004 - 6:28 am UTC
Tom
What about the following then:
drop table sbe1;
drop type myTableType;
drop type myScalarType;
create or replace type myScalarType as object
( org_id number(38),
reason_count number(38),
description varchar2(26)
)
/
create or replace type myTableType as table
of MyScalarType
/
CREATE TABLE SBE1 (RETURN_DATE DATE NOT NULL,
PAYMENT_ORG_ID NUMBER(38) NOT NULL,
st_count NUMBER(38),
sbe4 myTableType) nested table sbe4 store as n4;
insert into sbe1 values
(sysdate, 1, 2, myTableType( myScalarType( 1, 10, 'Under 16'),
myScalarType( 1, 20, 'Student'),
myScalarType( 1, 30, 'HC2')
));
insert into sbe1 values
(sysdate, 2, 4, myTableType( myScalarType( 2, 11, 'Under 16'),
myScalarType( 2, 22, 'Student'),
myScalarType( 2, 33, 'HC2')
));
insert into sbe1 values
(sysdate, 3, 8, myTableType( myScalarType( 3, 11, 'Under 16'),
myScalarType( 3, 22, 'Student'),
myScalarType( 3, 33, 'HC2')
));
insert into sbe1 values
(sysdate, 4, null, myTableType( myScalarType( 4, 11, 'Under 16'),
myScalarType( 4, 22, 'Student'),
myScalarType( 4, 33, 'HC2')
));
insert into sbe1 values
(sysdate, 5, 16, myTableType());
select sbe1.payment_org_id, sbe1.st_count,t2.org_id, t2.reason_count, t2.description
from sbe1, TABLE(sbe1.sbe4) t2
where sbe1.payment_org_id = t2.org_id (+);
This gives:
PAYMENT_ORG_ID ST_COUNT ORG_ID REASON_COUNT DESCRIPTION
-------------- ---------- ---------- ------------ --------------
1 2 1 10 Under 16
1 2 1 20 Student
1 2 1 30 HC2
2 4 2 11 Under 16
2 4 2 22 Student
2 4 2 33 HC2
3 8 3 11 Under 16
3 8 3 22 Student
3 8 3 33 HC2
4 4 11 Under 16
4 4 22 Student
4 4 33 HC2
It appears to have ignored the outer join.
i wanted the outer join so that the nested table which is null, when outer joined to the parent table, would still retrive the extra data from the null row, ie i want to see something like this:
PAYMENT_ORG_ID ST_COUNT ORG_ID REASON_COUNT DESCRIPTION
-------------- ---------- ---------- ------------ --------------
1 2 1 10 Under 16
1 2 1 20 Student
1 2 1 30 HC2
2 4 2 11 Under 16
2 4 2 22 Student
2 4 2 33 HC2
3 8 3 11 Under 16
3 8 3 22 Student
3 8 3 33 HC2
4 4 11 Under 16
4 4 22 Student
4 4 33 HC2
5 16 5
How would you get this type of result?
February 17, 2004 - 9:34 am UTC
It is not that the outer join was ignored, it isn't part of the equation!
ops$tkyte@ORA920PC> select sbe1.payment_org_id, sbe1.st_count,t2.org_id, t2.reason_count,
2 t2.description
3 from sbe1, TABLE(sbe1.sbe4) t2
4 /
PAYMENT_ORG_ID ST_COUNT ORG_ID REASON_COUNT DESCRIPTION
-------------- ---------- ---------- ------------ --------------------------
1 2 1 10 Under 16
1 2 1 20 Student
1 2 1 30 HC2
2 4 2 11 Under 16
2 4 2 22 Student
2 4 2 33 HC2
3 8 3 11 Under 16
3 8 3 22 Student
3 8 3 33 HC2
4 4 11 Under 16
4 4 22 Student
4 4 33 HC2
12 rows selected.
<b>TABLE() doesn't "join" like you think. that was just a predicate performed AFTER the fact...
but, back to the question -- how to do this? well, check this out:</b>
ops$tkyte@ORA920PC> select sbe1.payment_org_id, sbe1.st_count,t2.org_id, t2.reason_count, t2.description
2 from sbe1, <b>TABLE(sbe1.sbe4)(+) t2</b>
3 /
PAYMENT_ORG_ID ST_COUNT ORG_ID REASON_COUNT DESCRIPTION
-------------- ---------- ---------- ------------ --------------------------
1 2 1 10 Under 16
1 2 1 20 Student
1 2 1 30 HC2
2 4 2 11 Under 16
2 4 2 22 Student
2 4 2 33 HC2
3 8 3 11 Under 16
3 8 3 22 Student
3 8 3 33 HC2
4 4 11 Under 16
4 4 22 Student
4 4 33 HC2
5 16
13 rows selected.
see:
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96594/adobjbas.htm#458840 and specifically:
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96594/adobjbas.htm#466890
cool
dwl, February 17, 2004 - 11:38 am UTC
That is awesome, thanks very much!!
Nice
Ram, March 18, 2004 - 1:37 am UTC
Dear Sir,
Please have a look at the following objects and only one question
SQL> create type cust_ty as object(Bank_name varchar2(30),Bank_bal number(8,2))
2 /
Type created.
SQL> create type cust_tab as table of cust_ty;
2 /
Type created.
sql> create table customer(cust_name varchar2(30),cust_data cust_tab)
2* nested table cust_data store as cust_nt_data
SQL> /
Table created.
SQL> insert into customer values('King',
2 cust_tab(
3 cust_ty('HSBC',10000.90),
4 cust_ty('Std_chartered',5000.50),
5 cust_ty('NationalBank',450.75)
6 ));
1 row created.
SQL> ed
Wrote file afiedt.buf
1 insert into customer values('Jones',
2 cust_tab(
3 cust_ty('CommerzBank',1000.90),
4 cust_ty('Std_chartered',5000.50),
5 cust_ty('NationalBank',450.75),
6 cust_ty('ABN_AMRO',25000.55)
7* ))
SQL> /
1 row created.
My question is
1)Can the group functions be applied to nested table? For example
How to get the sum(bank_bal),min(bank_bal),max(bank_bal) for each customer?
You may use the data objects provided if you like.
Please do reply.
Bye!
March 18, 2004 - 7:28 am UTC
since you can flatten the set like this:
ops$tkyte@ORA9IR2> select c.cust_name, x.bank_name, x.bank_bal
2 from customer c, table( c.cust_data ) x;
CUST_NAME BANK_NAME BANK_BAL
------------------------------ ------------------------------ ----------
King HSBC 10000.9
King Std_chartered 5000.5
King NationalBank 450.75
Jones CommerzBank 1000.9
Jones Std_chartered 5000.5
Jones NationalBank 450.75
Jones ABN_AMRO 25000.55
7 rows selected.
<b>it becomes very natural to do this</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select c.cust_name, sum(x.bank_bal), min(x.bank_bal), max(x.bank_bal)
2 from customer c, table( c.cust_data ) x
3 group by c.cust_name
4 /
CUST_NAME SUM(X.BANK_BAL) MIN(X.BANK_BAL) MAX(X.BANK_BAL)
------------------------------ --------------- --------------- ---------------
Jones 31452.7 450.75 25000.55
King 15452.15 450.75 10000.9