PARTITION

Example 8-62 Retrieving the Names of Partitions

Assume that you have defined the following objects.

DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE time_parentrel RELATION time <time>
DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
PARTITION BY LIST (time)(PARTITION time_2003 VALUES -
('2003','Dec2003', 'Jan2003','31Dec2003','01Dec2003','31Jan2003','01Jan2003')-
<time product> PARTITION time_2002 VALUES -
('2002', 'Dec2002', 'Jan2002', '31Dec2002', '01Dec2002','31Jan2002','01Jan2002')-
 <time product>)
DEFINE sales DECIMAL <partition_sales_by_year<time product>>

Assume that these object have the values shown in the following report.

REPORT DOWN PARTITION(partition_sales_by_year) time product sales
 
PARTITION(PART
ITION_SALES_BY
_YEAR)            TIME     PRODUCT     SALES
-------------- ---------- ---------- ----------
TIME_2003      2003       00001              NA
TIME_2003      Dec2003    00001              NA
TIME_2003      Jan2003    00001              NA
TIME_2003      31Dec2003  00001           14.78
TIME_2003      01Dec2003  00001           15.52
TIME_2003      31Jan2003  00001           13.61
TIME_2003      01Jan2003  00001           10.39
TIME_2003      2003       00002              NA
TIME_2003      Dec2003    00002              NA
TIME_2003      Jan2003    00002              NA
TIME_2003      31Dec2003  00002           16.05
TIME_2003      01Dec2003  00002           12.27
TIME_2003      31Jan2003  00002           10.83
TIME_2003      01Jan2003  00002           11.07
TIME_2002      2002       00001              NA
TIME_2002      Dec2002    00001              NA
TIME_2002      Jan2002    00001              NA
TIME_2002      31Dec2002  00001           18.80
TIME_2002      01Dec2002  00001           13.64
TIME_2002      31Jan2002  00001           12.41
TIME_2002      01Jan2002  00001           16.97
TIME_2002      2002       00002              NA
TIME_2002      Dec2002    00002              NA
TIME_2002      Jan2002    00002              NA
TIME_2002      31Dec2002  00002           17.47
TIME_2002      01Dec2002  00002           16.58
TIME_2002      31Jan2002  00002           18.94
TIME_2002      01Jan2002  00002           18.36
         

As shown in the following code, you can use the PARTITION function to retrieve the names of the partition in which a a value is stored.

SHOW partition_sales_by_year
<2003, 00001>

LIMIT time to '31Jan2002'
SHOW PARTITION(partition_sales_by_year)
TIME_2002
LIMIT time to ALL
REPORT DOWN time PARTITION(partition_sales_by_year)
 
               PARTITION(PARTITION_S
               ----ALES_BY_YEAR)----
               -------PRODUCT-------
TIME             00001      00002
-------------- ---------- ----------
2003           TIME_2003  TIME_2003
2002           TIME_2002  TIME_2002
Dec2003        TIME_2003  TIME_2003
Jan2003        TIME_2003  TIME_2003
Dec2002        TIME_2002  TIME_2002
Jan2002        TIME_2002  TIME_2002
31Dec2003      TIME_2003  TIME_2003
01Dec2003      TIME_2003  TIME_2003
31Jan2003      TIME_2003  TIME_2003
01Jan2003      TIME_2003  TIME_2003
31Dec2002      TIME_2002  TIME_2002
01Dec2002      TIME_2002  TIME_2002
31Jan2002      TIME_2002  TIME_2002
01Jan2002      TIME_2002  TIME_2002