Handling null in Database

[Former Member]

Hi All,

I have an issue in IDOC - DB scenario.

Source field is optional

Target field is mandatory in DB and defined it as Integer in XI [datatype is Numeric in stored procedure]

I mapped the source field in such way that if it is null, then map Constant(Gave null value) else do the necessary transformation and used the mapwithdefault(null) and mapped it to Target field.

This is working fine when there is some value in the source field, but if source field is empty, there is an error.

NumberFormat exception in CommunicationChannel....

Its unable to convert the null to numeric format. But from Database end, I tested through query analyzer by populating some null value into that field and its working fine by accepting the null value.

Any pointers in this..

Regards,

Srini

[Former Member]

Closed.Changed the datatypes in Stored procedure.

[Former Member]

The is error is becausing you must be using FormatNumber in your mapping and when there is a null value in source field it throws the error.

You can solve this by writing an UDF.

[Former Member]

Hi Sarvesh,

Thanks for your response.

Yes We are using FormatNumber function..

Actually, the input element is (11,2) (Ex: -

---

11.20) Output should be (11.20).

To avoid those spaces we used Formatnumber as ( #.##) and now no spaces in the output.

As target is Mandatory, we used MapwithDefault in combination with Formatnumber...so ended up with error..

So you mean to say by writing UDF, we can resolve the trimming of spaces from the input field and at the same time we can insert the values to DB.

Regards,

Srini

[Former Member]

Actually you can try first without writing any UDF.

Since your target field is as integer therefore in you current mapping rather passing null (empty constant) just pass 0.00 because of integer field in your database it should be 0.00 instead of an enmpty value.

Regards,

Sarvesh

[Former Member]

Hi

Database data type is NUMBER so it will not except NULL. You need to pass 0.00 as the default value if no value is coming from the source.

Thanks

Gaurav