《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.9 NULLs
NULLs?? (page 291)
??? In SQL statements using Model SQL, values can be null for two reasons: null values in the existing
cells and references to non-existent cells. I will discuss the later scenario in this section. ?
By default, the reference to non-existent cells will return null values. In Listing 9-21, the rule in
line 10 is accessing the Sale column for the year =2002 and the week =1 using the clause? sale[2002,1].
There is no data in the sales_fact table for the year 2002 and so sale[2002,1] is accessing a non-
existent cell. Output in this listing is null due to the arithmetic operation with a null value.
In Line 4, I added a? KEEP NAV clause after the Model keyword explicitly even though? KEEP NAV is the
default value. NAV stands for Non Available Values and reference to a non-existent cell returns a null
value by default.
??? 使用Model子句的SQL，有两种情况会导致值为null：单元格存在null值和引用了非存在的单元格。在本节我讨论后面这种情况。默认情况下，引用非存在的单元格将返回null值。在列表9-21中，第10行的规则用子句sale[2002,1]访问year =2002 且 week =1的Sale列。在sales_fact表中没有2002年的数据，因此sale[2002,1]访问的是非存在的单元格。因为与null值进行算术运算所以该列的输出是null。在第4行，我在Model关键字之后显示的加KEEP NAV 子句，尽管KEEP NAV是默认值。NAV代表Non Available Values，而默认情况下引用非存在的单元格将返回null值。
Listing 9-21 KEEP NAV Example
?1?? select product, country,year,week,sale
?2??? from sales_fact
?3??? where country in ('Australia') and product = 'Xtend Memory'
?4??? model KEEP NAV return updated rows
?5??? partition by (product,country)
?6??? dimension by (year, week)
?7??? measures (sale)
?8??? rules sequential order(
?9 ? ? ??? sale[2001,1] order by year, week = sale[2001,1],
10 ?? ? ? sale[2002,1] order by year,week = sale[2001,1] + sale[2002,1]
11)
12 *???? order by product, country, year, week
PRODUCT??????????????????????? COUNTRY YEAR WEEK??? SALE
------------------------------???? ----------???? -----???? ----?????????? -------
Xtend Memory?????????????????? Australia?? 2001??? 1?????????????? 92
Xtend Memory?????????????????? Australia?? 2002??? 1
??? This default behavior can by modified using the IGNORE NAV clause. Listing 9-22 shows an example. If the non-existent cells are accessed, then 0 is returned for numeric columns and an empty string is returned for text columns instead of null values. You can see that the output in Listing 9-22 shows that a value of 92.26 is returned for the clause sale[2001,1] + sale[2002,1] as zero is returned for the non existing cell sale[2002,1].
??? 可用IGNORE NAV子句改变默认行为。列表9-22展示了一个例子。若访问了非存在的单元格，则替换null，数值列将返回0而文本列将返回空串。你可从列表9-22的输出中看到，对子句sale[2001,1] + sale[2002,1] 返回了值92.26，因为非存在的单元格sale[2002,1]返回0。
Listing 9-22. IGNORE NAV
1?? select product, country,year,week,sale
?2??? from sales_fact
?3??? where country in ('Australia') and product = 'Xtend Memory'
?4??? model IGNORE NAV return updated rows
?5??? partition by (product,country)
?6??? dimension by (year, week)
?7??? measures (sale)
?8??? rules sequential order(
?9 ? ? ??? sale[2001,1] order by year, week = sale[2001,1],
10 ?? ? ? sale[2002,1] order by year,week = sale[2001,1] + sale[2002,1]
11)
12 *???? order by product, country, year, week
PRODUCT??????????????????????? COUNTRY?? YEAR WEEK??? SALE
------------------------------???? ---------- ? ? ? -----?????? ----?????????? -------
Xtend Memory?????????????????? Australia????? 2001??? 1???????????? 92
Xtend Memory?????????????????? Australia????? 2002??? 1???????????? 92
??? The functions PRESENTV and PRESENTNNV are also useful in handling NULL values. Refer to the earlier section called "Iteration" for discussion and examples of these two functions.
??? 函数PRESENTV 和PRESENTNNV对于处理NULL值也很有用。参考之前“Iteration”一节对这两个函数讨论和例子。
?
?