Null Value Handling

[Former Member]

Hi all,

I have a problem in Handling null values using stored procedeure.

The Source is a proxy and the reciving is a stored Procedure.

The value when any field is not populated from the proxy then the value would by 0000-00-00(its a date field).Instead for 0000-00-00 we need to populate the stored procedure with value which will be a NULL in the DataBase.

I tried using Blank values but it throws an exception.

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'NDMS.NDMS_SAP_INTERFACE_PKG.NDMS_SAP_PROCESS' (structure 'StatementName'): java.lang.IllegalArgumentException

[justin_santhanam · Accepted solution]

Aditya,

Did u tried my logic in ur previous thread.

Best regards,

raj.

[Former Member · Accepted solution]

I tried tht raj

[justin_santhanam · Accepted solution]

Aditya,

Then wht happen, does it works or not?

Best regards,

raj.

[Former Member · Accepted solution]

No it doesnt work raj

[Former Member]

Thnks

[Former Member]

Hi all,

Could any one say me why i am getting the error of illegal argument.

Rgds

Aditya

[Former Member]

Hi,

You can always solve it by writing the SQL yourself using SQL_DML and then checking for the value of your parameters. If your parameter has no value XI will return value beginning with char '$'. You can for example use the following approach:

(SELECT CASE LEFT('$someParam$',1)

WHEN '$' THEN NULL

ELSE '$VisitID$'

END)

Best regards,

Daniel

[Former Member]

Should of cause look like

(SELECT CASE LEFT('$someParam$',1)

WHEN '$' THEN NULL

ELSE '$someParam$'

END)

[Former Member]

Hi all,

The if the date is not populated then we the date fields get populated automatically to 0000-00-00. Now when the data is in XI we have to map it in such a way to the target so that the data is represented as NULL in the data base.

Not constant NULL bt the data base value NULL.

Is there no way we can pass this blank value or do we need to do changes only in the stored procedure.

Rgds

Aditya

[former_member192892]

ok..

I'd sugggest this UDF

Gine the DATE element as an argument to this UDF

function(String a, Container)

{

String output = null;

if(!a.equalsIgnoreCase("0000-00-00"))

{

output = a;

}

return output;

}

[former_member192892]

One more thing adi,

Can u be sure that the value coming in the XML data in XI for the DATE field is 0000-00-00 and not anything else, because if anything else is coming, please change the UDF and give the coresponding value in the equalsIgnoreCase() method

[Former Member]

Hi all,

The problem is only when there are no values populated,

When the date fields are populated properly then i need to map the dates also

Thnks

Rgds

Aditya

[justin_santhanam]

Aditya,

Thats wht I told, Take a constant[] compare it with ur source field. Then use if then else. then part give constant[NULL] else part give the Source and finally map to the target. It will work.

Best regards,

raj.

[former_member192892]

Hi Aditya,

Is it a case where the date tag is missing from the input tag??? Or is it a case where date is populated as null or space in the input tag????

I'm sayin this because, if the input date tag is missing then u'd need 2 use

node f(n)----> exists()

Regards

Varun

[Former Member]

Aditya,

Exactly what I have tried to show you. Just ensure the occurance of the target field is 0..1.

@Raj,

Mapping a constant with value null is not equal to a null object.

Regards,

Jai Shankar

[justin_santhanam]

Jai,

Thanks. Ok, Please suggest me for this, If I wanna write SQL String using UDF ok. One of the values has to be null. So do u say for example If I write the query as below it won't work?

Insert into Emp(name,age,optional) values('J','25',null).

Best regards,

raj.

[former_member192892]

@ Raj,

The doubt i have is when u give a constant = null, Would it take it as a String "null"???

[Former Member]

Raj,

>>>Insert into Emp(name,age,optional) values('J','25',null).

Not sure about this SQL querry.

But when you pass a constant with value "null" inside mapping, it will be passed as a <b>String</b> constant with value null and not a null object.

Regards,

Jai Shankar

[justin_santhanam]

Varun & Jai,

I never overcome with such requirement, thats y i'm asking u guys.

Best regards,

raj.

[Former Member]

Hi Raj,

When u pass null it will be passed as a String constant instead pass null string '' this will probably solve the purpose

Regards

[Former Member]

Aditya,

Use a ifWithOutElse to check if the source is equal to 0000-00-00 and then use a not and provide it as input to condition block. In the then part map Date from source. Map the result to JDBC date tag.

Date(proxy) 
                     equals -- not ---- ifWithOutElse ---Date (JDBC)
                   /
Constant(0000-00-00)     

Could not show you the mapping of then block here.

Regards

Jai Shankar