Nullable to Non-Nullable

[Teej]

Hello folks --

I would like to pick all of your brains for a bit on this topic. It is generally known that if you have a Nullable column, and you need to convert it to Non-Nullable, you need to address what to do for those records with NULL values.

The one method I am aware of is using the Transform stage, and the following routine:

Code: Select all

If IsNull([link].[field]) Then [default value] Else [link].[field]

Is there any other methods you use to handle this? I know there is a Modify stage for 7.x, but what are the other options?

-T.J.

[kcbland]

Write an NVL type function or transform. It's reusable and most people know how it works:

Code: Select all

NVL(value, replacement value)

Fewer letters to type, reusable, nest-able, etc.

[Teej]

That would work for Server, but using the PX Transform stage? I am probably missing something big here if I am able to create a function that can be used by that stage.

-T.J.

[Eric]

The Sequental File Stage (Import/Export Operator) allows NULL values to be replaced with a VALUE. (using the Column Property -> Nullable -> Null Field Value).

[vzoubov]

Hello folks --

The one method I am aware of is using the Transform stage, and the following routine:

Code: Select all

If IsNull([link].[field]) Then [default value] Else [link].[field]

Is there any other methods you use to handle this? I know there is a Modify stage for 7.x, but what are the other options?

You can also check for a null value in a buildop:

Code: Select all

if(in.field_null())
{
      out.field = <default value>;
}
else
{
      out.field = in.field;
}

Vitali.

[ariear]

The Sequental File Stage (Import/Export Operator) allows NULL values to be replaced with a VALUE. (using the Column Property -> Nullable -> Null Field Value).

Somehow it doesn't work for me. I'm trying to process a delimited sequential file but I keep getting errors from the import phase that says something like -the null-value field must be the same size as the field (a specific one)- !

Then I went to the column definitions and went column by column and changed the default nullable property ! My god I have 390 columns
Isn't there any other way

ArieAR

[mmanes]

Hello folks --

I would like to pick all of your brains for a bit on this topic. It is generally known that if you have a Nullable column, and you need to convert it to Non-Nullable, you need to address what to do for those records with NULL values.

The one method I am aware of is using the Transform stage, and the following routine:

Code: Select all

If IsNull([link].[field]) Then [default value] Else [link].[field]

Is there any other methods you use to handle this? I know there is a Modify stage for 7.x, but what are the other options?

-T.J.

OK TJ it's my solution too... Ascential told that is the solution
PX 6.0

I apologize for my english

Bye