checking for null values

[bunny]

Hi all;

i am loading data from falt file (tab seperated ) using sequential file stage to database table using odbc stage.


in loading data from source to target, i need to do null checking on some fileds , if null those records are loaded in to reject file/table.i am using constraint to do this.

i have written my constraint like this;


isnull (filed1) and isnull( filed2) and isnull(field3) and isnull(field45) and isnull(field46) and isnull(field 47)

is the above expression right or wrong.

[martin]

Hi Bunny,

Your
IsNull(Feild1) And IsNull(Feild2) And IsNull(Feild3)
Will Work If All Feilds In a Entire Row Is Null
Instead If Go With
IsNull(Feild1) Or IsNull(Feild2) Or IsNull(Feild3)
Will Work If Any Feild In A Row Is Null

Try
Martin

[harithay]

thank you

martin

[T42]

Are you using APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL? Or are you using a specific field information to define NULL values from input sequential file?

How do you identify whether the input data on a file is a null?

[bunny]

--------------------------------------------------------------------------------

hi T42;

[Are you using
APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL? Or are you using a specific field information to define NULL values from input sequential file?

How do you identify whether the input data on a file is a null? ]


I am not using any one of above. i don't know how to identify whether the input data on a file is a nul, but i am not using a specific field information to define nul values from input sequetial file.

can u please explain how to define APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.


My problem is to reject records which contain null in any one of the field




thanks

[T42]

What does your source file use to say, 'This field is a null?'

[ElBandido]

I guess you can recognise null values in such a file, when you find a zero length string between two TABs.
In this case, I suggest to insert a transformer stage between your file and odbc stage, put your condition (say: IsNull(Field01) Or IsNull(Field02) and so on) in one of the output links, and mark this link as a reject link. It will make the flow easier to understand, and the data easier to handle.
Stefano

[T42]

I guess you can recognise null values in such a file, when you find a zero length string between two TABs.

The only way you can do this is if you trigger "APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL". It will not work otherwise.

Bunny mentioned that he/she does not know about this, so I am awaiting his response on what his input file is using to indicate "Null". Empty space is one such instance. There are many other solutions, and the correct answer depends on what this is.

Seriously, Bunny, you need some heavy duty DataStage training provided by Ascential. They cover subjects like this, and would go a long way in helping you understand how this works.