OBIEE 10G/11G - cast(NULL as INTEGER)

About

When debugging a report that doesn't retrieve result (with NULL or zero value) for an measure, you may remark that your measure has been replaced with a statement such as:

cast(NULL as  INTEGER)

This statement means that OBI can't find a physical relationship between the dimension and the fact table in your logical mapping for the query grain level.

See also: OBIEE - Cast function

Articles Related

How to debug it ?

To debug it, you have to:

• find the query grain level
• then find the logical tables source (Dimension and Fact) for this level
• then verify that the physical table sources defined (or mapped) in the logical table source have a physical relationship.

Example of log file

##############################################
-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',LOGLEVEL=5; SELECT Dimension.Attribute saw_0, "Fact".Count saw_1, "Fact"."Count" saw_2 FROM "SH" ORDER BY saw_0


+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:47

-------------------- General Query Info:
Repository: Star, Subject Area: SH, Presentation: SH


+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:47

-------------------- Logical Request (before navigation):

RqList 
    Dimension.Attribute as c1 GB,
    Count:[DAggr(Fact.Count by [ Dimension.Attribute] )] as c2 GB
OrderBy: c1 asc


+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:48

-------------------- Execution plan:

RqList <<57013829>> [for database 0:0,0] distinct 
    D1.c1 as c1 [for database 3023:18568,46]
Child Nodes (RqJoinSpec): <<57013850>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
    (
        RqList <<57013778>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
            D1.c1 as c1 GB [for database 3023:18568,46],
            cast(NULL as  INTEGER )  as c2 GB [for database 3023:18568,46],
        Child Nodes (RqJoinSpec): <<57013813>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
            (
                RqList <<57013785>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
                    PHYSICAL_DIMENSION_TABLE.Attribute as c1 GB [for database 3023:18568,46]
                Child Nodes (RqJoinSpec): <<57013796>> [for database 3023:18568:COBI_DM_LEV_CM_MP,46]
                    PHYSICAL_DIMENSION_TABLE T144549
            ) as D1
    ) as D1
OrderBy: c1 asc [for database 0:0,0]


+++gerar600:3eb40000:3eb4003b:----2011/08/02 10:14:48

-------------------- Sending query to database named COBI_DM_LEV_CM_MP (id: <<57013778>>):
WITH 
SAWITH0 AS (select distinct T144549.ATTRIBUTE as c1
from 
     PHYSICAL_DIMENSION_TABLE T144549)
select distinct SAWITH0.c1 as c1,
     cast(NULL as  INTEGER  ) as c2
from 
     SAWITH0