Data services: to_date inhibits push down

[Former Member]

Hi all

Has anyone else encountered DS (4.2 SP9) to be unable to push down when using to_date function (contrary to what is documented in note 2212730)? Please find attached test case: df-patient-il-ini-resuffix-to-xml.txt (if you are curious why I did go to such renaming lengths with a tool costing hundreds of thousands of Swiss francs, read below. To me this is just inconceivable!).

The flow does not seem to be overly complicated:

Setting VALID_UNTIL to sysdate() supports full push down (not what I call beatifull code but fully pushed down none the less).

insert
    /*+ APPEND */
    into
        "IL"."PATIENT"(
            "T_JOB_ID",
            "T_FLOW_NAME",
            "T_BATCH_ID",
            "FK_01_MPGE_T_RECORD_ID",
            "FK_01_MPSS_T_RECORD_ID",
            "FK_02_MPSS_T_RECORD_ID",
            "PATIENT_ID",
            "BIRTH_DATE",
            "F_INTERNAL_EMPLOYEE",
            "VALID_FROM",
            "VALID_UNTIL"
        ) select
            $VG_JOB_ID T_JOB_ID,
            $I_DATAFLOW_NAME T_FLOW_NAME,
            $VG_BATCH_ID T_BATCH_ID,
            "MAP_GENDER"."T_RECORD_ID" FK_01_MPGE_T_RECORD_ID,
            "MAP_SOURCE_SYSTEM_1_1"."T_RECORD_ID" FK_01_MPSS_T_RECORD_ID,
            "MAP_SOURCE_SYSTEM_1"."T_RECORD_ID" FK_02_MPSS_T_RECORD_ID,
            "HE#PATIENT"."PID" PATIENT_ID,
            "HE#PATIENT"."PATIENTS_BIRTH_DATE" BIRTH_DATE,
            (
                (
                    (
                        case
                            when(
                                "HE#PATIENT"."CLASSIFICATION" = 1
                            ) then 0
                            else 1
                        end
                    )
                )
            ) F_INTERNAL_EMPLOYEE,
            sysdate VALID_FROM,
            sysdate VALID_UNTIL
        from
            (
                (
                    "LZ"."HE#PATIENT" "HE#PATIENT"
                left outer join "IL"."MAP_GENDER" "MAP_GENDER" on
                    (
                        nvl( "HE#PATIENT"."PATIENTS_GENDER", 'U' )= "MAP_GENDER"."CD_USB_HE"
                    )
                    and(
                        "MAP_GENDER"."VALID_FROM" <= sysdate
                    )
                    and(
                        "MAP_GENDER"."VALID_UNTIL" >= sysdate
                    )
                    and(
                        (
                            sysdate >= "MAP_GENDER"."T_APPROVED_ON"
                        )
                        or(
                            1 = 1
                        )
                    )
                )
            left outer join "IL"."MAP_SOURCE_SYSTEM" "MAP_SOURCE_SYSTEM_1_1" on
                (
                    "HE#PATIENT"."T_SOURCE" = "MAP_SOURCE_SYSTEM_1_1"."CD_USB_CDWH"
                )
                and(
                    "MAP_SOURCE_SYSTEM_1_1"."VALID_FROM" <= sysdate
                )
                and(
                    "MAP_SOURCE_SYSTEM_1_1"."VALID_UNTIL" >= sysdate
                )
                and(
                    (
                        sysdate >= "MAP_SOURCE_SYSTEM_1_1"."T_APPROVED_ON"
                    )
                    or(
                        1 = 1
                    )
                )
            )
        left outer join "IL"."MAP_SOURCE_SYSTEM" "MAP_SOURCE_SYSTEM_1" on
            (
                "HE#PATIENT"."SOURCE_SYSTEM" = "MAP_SOURCE_SYSTEM_1"."CD_USB_HE"
            )
            and(
                "MAP_SOURCE_SYSTEM_1"."VALID_FROM" <= sysdate
            )
            and(
                "MAP_SOURCE_SYSTEM_1"."VALID_UNTIL" >= sysdate
            )
            and(
                (
                    sysdate >= "MAP_SOURCE_SYSTEM_1"."T_APPROVED_ON"
                )
                or(
                    1 = 1
                )
            );

Setting VALID_UNTIL to

to_date('9999-12-31 23:59:59.999999999', 'yyyy-mm-dd hh24:mi:ss.ff') inhibits full push down (BLIMEY! What is this code supposed to be?):

1. Why does DS take in all the dates from the mapping tables into the engine?
2. Why does DS convert the dates of 1. to characters?

select
    (
        (
            (
                case
                    when(
                        "HE#PATIENT"."CLASSIFICATION" = 1
                    ) then 0
                    else 1
                end
            )
        )
    ),
    "MAP_GENDER"."T_RECORD_ID",
    "MAP_SOURCE_SYSTEM_1_1"."T_RECORD_ID",
    "MAP_SOURCE_SYSTEM_1"."T_RECORD_ID",
    "HE#PATIENT"."PID",
    "HE#PATIENT"."PATIENTS_BIRTH_DATE",
    "HE#PATIENT"."SOURCE_SYSTEM",
    "MAP_SOURCE_SYSTEM_1"."CD_USB_HE",
    to_char( "MAP_SOURCE_SYSTEM_1"."VALID_FROM", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_SOURCE_SYSTEM_1"."VALID_UNTIL", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_SOURCE_SYSTEM_1"."T_APPROVED_ON", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    "HE#PATIENT"."T_SOURCE",
    "MAP_SOURCE_SYSTEM_1_1"."CD_USB_CDWH",
    to_char( "MAP_SOURCE_SYSTEM_1_1"."VALID_FROM", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_SOURCE_SYSTEM_1_1"."VALID_UNTIL", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_SOURCE_SYSTEM_1_1"."T_APPROVED_ON", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    "HE#PATIENT"."PATIENTS_GENDER",
    "MAP_GENDER"."CD_USB_HE",
    to_char( "MAP_GENDER"."VALID_FROM", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_GENDER"."VALID_UNTIL", 'yyyy.mm.dd hh24:mi:ss.ff' ),
    to_char( "MAP_GENDER"."T_APPROVED_ON", 'yyyy.mm.dd hh24:mi:ss.ff' )
from
    (
        (
            "LZ"."HE#PATIENT" "HE#PATIENT"
        left outer join "IL"."MAP_GENDER" "MAP_GENDER" on
            (
                nvl( "HE#PATIENT"."PATIENTS_GENDER", 'U' )= "MAP_GENDER"."CD_USB_HE"
            )
            and(
                "MAP_GENDER"."VALID_FROM" <= sysdate
            )
            and(
                "MAP_GENDER"."VALID_UNTIL" >= sysdate
            )
            and(
                (
                    sysdate >= "MAP_GENDER"."T_APPROVED_ON"
                )
                or(
                    1 = 1
                )
            )
        )
    left outer join "IL"."MAP_SOURCE_SYSTEM" "MAP_SOURCE_SYSTEM_1_1" on
        (
            "HE#PATIENT"."T_SOURCE" = "MAP_SOURCE_SYSTEM_1_1"."CD_USB_CDWH"
        )
        and(
            "MAP_SOURCE_SYSTEM_1_1"."VALID_FROM" <= sysdate
        )
        and(
            "MAP_SOURCE_SYSTEM_1_1"."VALID_UNTIL" >= sysdate
        )
        and(
            (
                sysdate >= "MAP_SOURCE_SYSTEM_1_1"."T_APPROVED_ON"
            )
            or(
                1 = 1
            )
        )
    )
left outer join "IL"."MAP_SOURCE_SYSTEM" "MAP_SOURCE_SYSTEM_1" on
    (
        "HE#PATIENT"."SOURCE_SYSTEM" = "MAP_SOURCE_SYSTEM_1"."CD_USB_HE"
    )
    and(
        "MAP_SOURCE_SYSTEM_1"."VALID_FROM" <= sysdate
    )
    and(
        "MAP_SOURCE_SYSTEM_1"."VALID_UNTIL" >= sysdate
    )
    and(
        (
            sysdate >= "MAP_SOURCE_SYSTEM_1"."T_APPROVED_ON"
        )
        or(
            1 = 1
        )
    );

Why can't I switch to html to get out of code?!? Anyway the story behind renaming the test case file. I first wanted to import atl file but it was inhibited:

<img alt="">

Ok, export again as xml. xml is supported...
<img alt="">
<img alt="">

... I am having a fit instead!

It is so sad. (copycatted from Donald Trump)

Confound this impudence of the vendor of a tool costing hundreds of thousands of Swiss francs!

[former_member187605 · Accepted solution]

to_date is only pushed down when the target datatype is date or datetime. Generated code for MS SQL Server database:

INSERT INTO "TGT" ( "PARENT" , "CHILD" , "DAT1" , "DAT2" , "DAT3" , "TS" )

SELECT "SRC"."PARENT" , "SRC"."CHILD" , CONVERT(datetime2, "SRC"."CHILD" , 112) , CONVERT(datetime2, '99991231', 112) , "SRC"."DAT3" , NULL

FROM "dbo"."SRC"

No SQL pushdown of to_date in combination with a timestamp.

[Former Member]

For dates/timestamps that do not change during the execution of a data flow, you can work around this with setting up a (global) date/timestamp variable that is being passed by parameter to the query. In the query use the parameter instead of calling the function.

[Former Member]

Thanks for the answer. So we stick with the work around.