History Preserving- Valid to date as Null

dataservices job design
1

Hi,

For active records, I want to populate the Valid To field as Null rather that the default 9999/12/31 via History preserving transform. By default it converts NULL to 1900/01/01.
Is it possible to retain it as NULL?
DS- BODS 4.1 Sp1

Thanks

rajnia1 (BOB member since 2011-11-17)

2

Why do you want to do so?
Having it set with an dummy expiry date would perform faster in queries than testing for whether a record is null?

Mak 1 :uk: (BOB member since 2005-01-06)

3

Yes. I aware about the downside of using NULL . I was just curious if there is a provision for NULL on History Preserving.?

rajnia1 (BOB member since 2011-11-17)

4

Try using a global variable (of data type datetime) that is set to NULL.

eganjp :us: (BOB member since 2007-09-12)

5

Global variable also doesn’t return null. The same default value is returned…

rajnia1 (BOB member since 2011-11-17)

6

Is it possible that it is your database server forcing null to a date value?

eganjp :us: (BOB member since 2007-09-12)

7

I don’t think so…BODS allows null on other datetime fields…such as while mapping datetime columns to NULL.
It converts to default value only on History Preserving…

rajnia1 (BOB member since 2011-11-17)

8

As was said above by Mak 1, I don’t recommend using a NULL in this case.

eganjp :us: (BOB member since 2007-09-12)

9

This is SAP Data Services behavior.
Please vote up an Enhancement to set the SAP NULL TO NULL on the job level on the SAP Idea place
https://ideas.sap.com/D8223

Here is some documentation on SAP Null values:
SAP Data Services Supplement for SAP - DS 4.2 SP8
http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_sap_en.pdf

9.3.2 Working with null values from SAP applications (P197)
9.3.4 Null dates (this seems to be talking about ADF)
The concept of a null date in SAP applications is represented by a value called INITIAL that is a property of every SAP application In the default configuration of Data Services (where Convert SAP null to null is disabled), INITIAL dates are written to an ABAP data file as NULLs.

In ABAP data files, NULLs are represented by empty strings. They are then read by the parent data flow, which converts every empty string to a single space. If passed through again without data type or value change, Data Services converts the single blank into 01-JAN-1900 before loading it into a target.

JDRoble (BOB member since 2009-06-22)