Cognos Analytics

I wonder if anyone can shed light on why DQM handles the nullif() function differently depending on the datatypes? I use this function to get a null in a query.

nullif( 1, 1 ) is sent to the database. If I create a simple query with this calculation, the native sql is:  

SELECT DISTINCT 
    "PRESENTATION_SHARED_D_CAL0"."CAL_YEAR_NAME" AS "Year0", 
    null AS "Data_Item1"
FROM
    "PRESENTATION_SHARED"."D_CAL" "PRESENTATION_SHARED_D_CAL0"

compare that to when a nullif( 'a', 'a') is used. When the null is added with local processing.

If anyone knows of an alternative way of coding a null into a calc which doesnt require local processing or db specific functions please post. I try an avoid {null} as thats a db thing.

Thanks.

------------------------------
Marc Reed
------------------------------

Answering my own question in case anyone else is interested. 

I am now using:

case 0 when 1 then 'a' end.

This results in a native query with all processing passed to the database:
WITH 
"Query1" AS 
    (
    SELECT DISTINCT 
        "PRESENTATION_SHARED_D_CAL0"."CAL_YEAR_NAME" AS "Year0", 
        CAST(NULL AS VARCHAR2(1)) AS "Data_Item1"
    FROM
        "PRESENTATION_SHARED"."D_CAL" "PRESENTATION_SHARED_D_CAL0"
    )
SELECT DISTINCT 
    "Union1"."Year0" AS "Year0", 
    "Union1"."Data_Item1" AS "Data_Item1"
FROM
    (
    SELECT
        "Query1"."Year0" AS "Year0", 
        "Query1"."Data_Item1" AS "Data_Item1"
    FROM
        "Query1"
    
    UNION
    
    SELECT
        "Query1"."Year0" AS "Year0", 
        "Query1"."Data_Item1" AS "Data_Item1"
    FROM
        "Query1"
    ) "Union1"

Couple of comments.

Dynamic Query applies a concept known as constant folding, where expressions can be re-written/simplified.

Logically nullif (1,1) can be re-written as a value-expression representing a null value.

Suggest you open a support ticket.

I recommend that persons do not write NULL in expressions. Instead write cast ( null , <type>).
NULL is a value-specification where engines would be left to try to infer what data type it should use.
Subject to the scenario/vendor, it may not be able to infer a type and throw an error.
Some may assume varchar as a hail-mary play.