The PARTITION function returns the name of the partition in which a value is stored.
An expression that represents one or more values of the partition template (for example, the name of a partition template or a QDR).
Example 8-60 Retrieving the Name of a Partition
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 value is stored.
SHOW partition_sales_by_year
<2003, 00001>
" Use a QDR to identify the partition of a specific time value
SHOW PARTITION(partition_sales_by_year (time '31Jan2002'))
TIME_2002
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