Null Values in WhereClause Sheet

[bosh.yang]

Dears,

Is there any way to express null value in define.xml(see below) using P21 Community Standard template?

In the define.xml above, I tried VSEVINTX EQ NULL and VSEVINTX EQ EMPTY, both doesn’t work, and then I tried VSEVINTX EQ , I get the screenshot above, however, when validating the define.xml file, an issue was raised below, May I confirm it is a bug when validating or we should use other text for null value instead of a blank space in Value column in WhereClauses sheet?

[Trevor_Mankus]

Welcome to our forum @bosh.yang

Our P21C tool does not support null values in where clause yet. It’s something we have listed as an enhancement to make in the future.

One workaround would be to use the NOT IN or NE functionality and exclude the other values.

You could also manually make adjustments to the define.xml to adapt the generated XML output from P21C to handle this. I can help you with that if you’d like. You want to make sure you have something like this in your code. Note the middle line <CheckValue/>. I think that is what is missing in your P21C generated define.xml document.

<def:WhereClauseDef OID="WC.TS.TSPARMCD.EQ.">
  <RangeCheck SoftHard="Soft" def:ItemOID="IT.TS.TSPARMCD" Comparator="EQ">
    <CheckValue/>
  </RangeCheck>
</def:WhereClauseDef>

[bosh.yang]

Dear Trevor,

Thanks for your prompt response, we can use NOT IN or NE as an operator, however, when there are lots of values for that variable, it will take some time to enter a long list. Anyway, it’s really great to hear that it’s already in the enhancement list, that’s will make things easy, waiting for the upgrading version and THANK YOU again.

[harald.goldner]

@bosh.yang

I personally use P21C template with null values and do not get any validation issues. I am using 2 double quotes as placeholder for NULL and it seems to work:

Seems to work with both numeric and character variables.

Best, Harald

[bosh.yang]

@harald.goldner

Harald

Hi Harald,

Thanks for the suggestion. However, when I tried 2 double quotes as placeholder for character variable, it displays like below, I think it’s not what we expect, we still need to do more exploration. Anyway, thanks for your reply.