![]() Previous |
![]() Next |
This statement returns Single Item
or Value Pack
depending on whether the PACKAGE
attribute of the PRODUCT
dimension is null or has a value:
CASE PRODUCT.PACKAGE WHEN NULL THEN 'Single Item' ELSE 'Value Pack'END
Product | Package | Category |
---|---|---|
1.44MB External 3.5" Diskette | Executive | Value Pack |
1GB USB Drive | -- | Single Item |
512MB USB Drive | -- | Single Item |
56Kbps V.90 Type II Modem | Executive | Value Pack |
56Kbps V.92 Type II Fax/Modem | Laptop Value Pack | Value Pack |
Deluxe Mouse | Executive | Value Pack |
Envoy Ambassador | -- | Single Item |
Envoy Executive | Executive | Value Pack |
Envoy External Keyboard | Executive | Value Pack |
Envoy Standard Laptop | Value Pack | Value Pack |
External - DVD-RW - 8X | Executive | Value Pack |
External 101-key keyboard | Multimedia | Value Pack |
External 48X CD-ROM | -- | Single Item |
Internal - DVD-RW - 6X | Multimedia | Value Pack |
The next statement increases the unit price by 20%, truncated to the nearest dollar, if the difference between price and cost is less than 10%. Otherwise, it returns the current unit price.
CASE WHEN PRICE_CUBE.UNIT_PRICE < PRICE_CUBE.UNIT_COST * 1.1 THEN TRUNC(PRICE_CUBE.UNIT_COST * 1.2) ELSE PRICE_CUBE.UNIT_PRICE END
Product | Cost | Old Price |
---|---|---|
1GB USB Drive | 483.55 | 546.83 |
512MB USB Drive | 234.69 | 275.91 |
56Kbps V.90 Type II Modem | 135.72 | 158.58 |
56Kbps V.92 Type II Fax/Modem | 95.01 | 111.08 |
Envoy Ambassador | 2686.01 | 2850.88 |
Envoy Executive | 2799.80 | 2943.96 |
Envoy Standard | 1933.82 | 1921.62 |
External - DVD-RW - 8X | 263.83 | 300.34 |
External 48X CD-ROM | 223.11 | 254.15 |
Internal - DVD-RW - 6X | 134.46 | 160.18 |
Internal 48X CD-ROM | 108.32 | 127.54 |
Internal 48X CD-ROM USB | 46.00 | 68.54 |
Monitor- 17"Super VGA | 228.53 | 269.70 |
Monitor- 19"Super VGA | 445.04 | 504.84 |
Sentinel Financial | 1685.72 | 1764.14 |
Sentinel Multimedia | 1849.17 | 1932.54 |
Sentinel Standard | 1572.98 | 1610.53 |
The next example creates a Sales Budget calculated measure by multiplying Sales from the previous year by 1.06 for a 6% increase. The detail levels of all dimensions are excluded from the calculation. The Budget is projected only using data from 2006 or later.
CASE WHEN TIME.END_DATE >= TO_DATE('01-JAN-2006') AND TIME.LEVEL_NAME IN ('CALENDAR_YEAR', 'CALENDAR_QUARTER') AND PRODUCT.LEVEL_NAME != 'ITEM' AND CUSTOMER.LEVEL_NAME IN ('TOTAL', 'REGION', 'WAREHOUSE') THEN TRUNC(LAG(UNITS_CUBE.SALES, 1) OVER HIERARCHY (TIME.CALENDAR BY ANCESTOR AT LEVEL TIME.CALENDAR.CALENDAR_YEAR POSITION FROM BEGINNING) * 1.06) ELSE NULL END
Product | Time | Sales |
---|---|---|
Hardware | Q1.05 | 28172590 |
Hardware | Q2.05 | 34520379 |
Hardware | Q3.05 | 29466573 |
Hardware | Q4.05 | 32031795 |
Hardware | Q1.06 | 32711891 |
Hardware | Q2.06 | 33637473 |
Hardware | Q3.06 | 29227635 |
Hardware | Q4.06 | 31319881 |
Hardware | Q1.07 | -- |
Hardware | Q2.07 | -- |
Hardware | Q3.07 | -- |
Hardware | Q4.07 | -- |