Skip Headers
Previous
Previous
 
Next
Next

Examples

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 --