NULL is Ignored in Rule

[former_member196240]

Hi Experts.

I have written a below code and its working fine except one case. When the columns has null values in the SQL view, the IS is not checking up such values in the failed results. Though its violating the rule.

Here's my code, may be its wrong or it needs corrected.

BEGIN

IF ($ProcurementMethod = 'PO/SO'  And ($Source_Costing_SPK != $Source_MRPController))

RETURN FALSE;

ELSE IF ($ProcurementMethod = 'STO' OR $ProcurementMethod = 'STSA' AND ($Costing_SPK != ' '))

RETURN FALSE;                               

ELSE IF ($ProcurementMethod = 'STO' OR $ProcurementMethod = 'STSA' AND ($Source_Costing_SPK !=$Source_MRP_SPK))

RETURN FALSE;

ELSE

RETURN TRUE;

END

Appreciate your thoughts.

thanks in advance,

Vinay

[Former Member · Accepted solution]

if the column value can be null then your rule should explicitly check for IS NULL or IS NOT NULL, you can't use = and !=  for NULLs

[former_member196240 · Accepted solution]

Hi Manoj,

Lets take this as example for the better explanation of my issue:

IF ($ProcurementMethod = 'PO/SO'  AND ($Source_Costing_SPK = $Source_MRPController))

this code works absolutely fine as long as there is no NULL values in $Source_Costing_SPK  or $Source_MRPController columns.

Ex: When $Source_Costing_SPK = US91 AND $Source_MRPController = NULL

Per the example it should fail right, because those two column values are not same.

I know there is a way but I lack a programming expertise.If you can help me that would be great.

thanks in advance.

[adrian_storen · Accepted solution]

https://ideas.sap.com/ct/ct_a_view_idea.bix?c=9F6A80F0-02CE-4DBB-8533-DF30CFE6CA2A&idea_id=89897BDC-...

Vinay,

This is by design.  I raised this with SAP and DI rules assume all fields are populated, and a NULL in any field will produce a failure in this scenario.  I raised this idea to hopefully change the current design.

For now, all rules will need to cater for all fields potentially having a NULL or you introduce an NVL to convert from NULL in all parameters.

Feel free to vote for the idea and with any luck SAP will change the functionality at some point.

regards

Adrian

[Former Member · Accepted solution]

yes, it should fail if one of them is NULL, with the current logic it will also fail if both of them are NULL, I would expect it to pass if both are NULL

for it to pass in case both $Source_Costing_SPK  and $Source_MRPController are NULL, change the condition to as below

if ($ProcurementMethod = 'PO/SO'  AND (($Source_Costing_SPK = $Source_MRPController) OR ($Source_Costing_SPK IS NULL AND $Source_MRPController IS NULL)))

[former_member196240 · Accepted solution]

I think have fixed the issue which I had for the null values. Not sure what was the exact reason, I tried the below code and then ran the rule task. Now the IS failed results have the records that had violated the rule.

BEGIN

IF ($ProcurementMethod = 'PO/SO' AND ($Source_Costing_SPK = $Source_MRPController))

RETURN TRUE;

ELSE IF ($ProcurementMethod = 'STO' AND ($Source_Costing_SPK = $Source_MRP_SPK))

RETURN TRUE;

ELSE IF ($ProcurementMethod = 'STSA' AND ($Source_Costing_SPK = $Source_MRP_SPK))

RETURN TRUE;

ELSE IF ($ProcurementMethod = 'STO' AND ($Costing_SPK = ' '))

RETURN TRUE;

ELSE IF ($ProcurementMethod = 'STSA' AND ($Costing_SPK = ' '))

RETURN TRUE;

ELSE

RETURN FALSE;

END