Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Re : [Mondrian] NULL literal

  1. #1
    michael bienstein Guest

    Default Re : [Mondrian] NULL literal

    My understanding is as follows:
    NULL as a value (not literal) is for Members that do not exist physically but do logically such as [Geography].[All Geography].Parent. When you pull a cell value for which one member of the context is NULL, you get a NULL value as a numeric (or String etc) for the cell value as opposed to an EMPTY value which is when the membes all exist but there is no value in the cube at that position.

    After that there's lots of tricky things you can do with numeric NULL values like addition, multiplication etc E.g. A is a numeric expression that evaluates to NULL and B is a numeric expression that doesn't. What does A+B evaluate to?

    As for the 'IS NULL' construct, that's to test Member expression and numeric expression for having the NULL value given that NULL=NULL yields false. You can also use NULL explicitly in Range function:
    [Time].[1997].FirstChild : NULL

    Here's an example of an MDX query that should test a problem (not tested anywhere):

    WITH MEMBER [Time].[1997].[Test] AS '[Time].[1997]+0'
    SELECT
    {[Time].[1997], [Time].[1997].[Test]} on COLUMNS,
    {[Store].[All Stores].Parent} on ROWS
    FROM [Warehouse Sales]

    Michael

    PS: I've got code for running multiple tasks going but without test cases. Found some time. Taking longer than expected. Will post by next week but don't want to disturb your efforts to release.











    ___________________________________________________________________________
    D

  2. #2
    Richard Emberson Guest

    Default Re: Re : [Mondrian] NULL literal

    I've never seen the MDX specification - I think that
    whatever MS does is the spec. Anyway,
    Mosha Pasumansky is the author(??) of MDX and
    from a couple of his web pages I find:

    "First, it notices, that for A*B will be NULL
    if either A or B is NULL."
    - half way down the page
    http://www.mosha.com/msolap/articles...ation_perf.htm


    "and NULL/NULL is defined as NULL in MDX."
    1/8 of the way down the page
    http://www.feedshow.com/show_items-f...631652cff7ea79

    So I assume that for expressions like a+b,a-b,a*b,a/b if either
    a or b are NULL then:
    NULL+b == b
    a + NULL == a
    NULL - b == -b
    a - NULL == a
    a*NULL == NULL
    NULL*b == NULL
    a/NULL == NULL
    NULL/b == NULL
    and if both are NULL then the result is NULL.

    The NULL Literal when compiled become an ConstantCalc
    instance.
    The change I believe is needed is in the ConstantCalc
    constructor. If the constructor parameter 'o' which
    is of type Object is a Java null, then the instance
    variables have the values:

    Object o = null;
    int i = FunUtil.IntegerNull
    double d = FunUtil.DoubleNull

    Richard


    FYI

    For Empty I've found this page:
    http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

    specifically:
    "When the empty cell value is an operand for any of the numeric
    operators (+, -, *, /), it behaves like the number zero."

    michael bienstein wrote:[color=blue]
    > My understanding is as follows:
    > NULL as a value (not literal) is for Members that do not exist
    > physically but do logically such as [Geography].[All Geography].Parent.
    > When you pull a cell value for which one member of the context is NULL,
    > you get a NULL value as a numeric (or String etc) for the cell value as
    > opposed to an EMPTY value which is when the membes all exist but there
    > is no value in the cube at that position.
    >
    > After that there's lots of tricky things you can do with numeric NULL
    > values like addition, multiplication etc E.g. A is a numeric expression
    > that evaluates to NULL and B is a numeric expression that doesn't. What
    > does A+B evaluate to?
    >
    > As for the 'IS NULL' construct, that's to test Member expression and
    > numeric expression for having the NULL value given that NULL=NULL yields
    > false. You can also use NULL explicitly in Range function:
    > [Time].[1997].FirstChild : NULL
    >
    > Here's an example of an MDX query that should test a problem (not tested
    > anywhere):
    >
    > WITH MEMBER [Time].[1997].[Test] AS '[Time].[1997]+0'
    > SELECT
    > {[Time].[1997], [Time].[1997].[Test]} on COLUMNS,
    > {[Store].[All Stores].Parent} on ROWS
    > FROM [Warehouse Sales]
    >
    > Michael
    >
    > PS: I've got code for running multiple tasks going but without test
    > cases. Found some time. Taking longer than expected. Will post by
    > next week but don't want to disturb your efforts to release.
    >
    >
    >
    >
    > ------------------------------------------------------------------------
    > D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.