Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
POWERMULTISET_BY_CARDINALITY
takes as input a nested table and a cardinality and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the nested table of the specified cardinality.
expr
can be any expression that evaluates to a nested table.
cardinality
can be any positive integer.
If expr
resolves to null, then Oracle Database returns NULL
.
If expr
resolves to a nested table that is empty, then Oracle returns an error.
The element types of the nested table must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.
Note:
This function is not supported in PL/SQL.First, create a data type that is a nested table of the cust_address_tab_type
data type:
CREATE TYPE cust_address_tab_tab_typ AS TABLE OF cust_address_tab_typ; /
Next, duplicate the elements in all the nested table rows to increase the cardinality of the nested table rows to 2:
UPDATE customers_demo SET cust_address_ntab = cust_address_ntab MULTISET UNION cust_address_ntab;
Now, select the nested table column cust_address_ntab
from the customers_demo
table using the POWERMULTISET_BY_CARDINALITY
function:
SELECT CAST(POWERMULTISET_BY_CARDINALITY(cust_address_ntab, 2) AS cust_address_tab_tab_typ) FROM customers_demo; CAST(POWERMULTISET_BY_CARDINALITY(CUST_ADDRESS_NTAB,2) AS CUST_ADDRESS_TAB_TAB_TYP) (STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) ---------------------------------------------------------------------------------------- CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP (CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'), CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP (CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'), CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP (CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'), CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))) . . .
The preceding example requires the customers_demo
table and a nested table column containing data. Refer to "Multiset Operators" to create this table and nested table columns.