Data Selection for Nulls or Blank

[deo]

I want to have a data selection on a field where the contents can be either null or blank. I also have several AND statements in the same data selection. Is is possible to have a list containing null and blank in one statement?

 

[Bill Dotson]

I believe you'll have to repeat all of your AND conditions after you put in the OR, beginning with "OR BC Document (Order No., Invoice...) is equal to RV DOCO" all the way through the last one, which will be "is equal to <blank>" instead of "is equal to <null>."

It's either that, or invent a way to use parentheses in data selection, and JDE doesn't allow you to specify <blank> or <null> as list items.

 

[Adrian_Chimirel]

Or a shorter answer: no, it is not possible.

 

[Lightning_McQueen]

How about try "Less than or equal to BLANK". Null would be considered less than blank.

 

[Bill Dotson]

That actually depends on the database. Some databases handle nulls differently than scalar values.

 

[Larry_Jones]

In fact all major databases (including the one's that JDE supports) consider null to be a unknown value; it is not the same as zero or all blanks, and therefore cannot be used in any comparison other than to test if equal to NULL or not equal to null. The key is that the value is UNKNOWN.

 

[deo]

I am new at JDE programming and appreciate your advice and suggestions. I think I wil test the <= blanks idea or I will do the compare outside of the data selection. Thank you everyone.

 

[Bill Dotson]

You must be new to programming, period.

Null isn't less than blank, equal to it or greater than it. Its value is undefined, at best. Testing for <=blank is very unlikely to get the results you desire.

Doing the comparison outside of the data selection makes it look like you're lazy, foolish or both, since that will clearly have a negative performance impact on your application. You'd rather write code to do something that can be done automatically by the database, because it's too much trouble to key in the correct data selection?

 

[Frosty the Coder]

Maybe it's just dumb luck, which I've made a career out of,
BUT I've been using <= blanks for several years on various releases of E1.
I haven't NOTICED it not working as I want.....

 

[Frosty the Coder]

Post script - I'm both lazy AND foolish.
I thought that was part of being a developer.....

 

[Bill Dotson]

I've seen that work in JDE too, but I still don't think it's right. Maybe the JDE middleware protects you from yourself in that regard. I'd rather be able to see the correct logic in the data selection, rather than guessing, and hoping that the middleware continues to behave the same. Will someone, who comes along after you, know that your logic also excludes or includes null values?

 

[Larry_Jones]

Gene,

the reason your comparison works is that generic JDE tables do not have null values in their columns. One of the layers always ensures that blank or zero (depending on datatype) is written to the table.

Of course its still possible to have null values when updating by using non jde toolset (SQL, etc) or when dealing with foreign tables . . .

 

[Zoltan_Gyimesi]

Hi deo,

First of all, welcome aboard of JDEList.

If your selection is reside in the Set Selection of a section in Report Designer or it is on version level selection, then move your Blank OR Null part to the report template, into the Init Section event of the affected event using the SetSelection Append Flag and Set User Selection system functions. This make you one level bracket for the selection as:

(User Selection) AND (Programmed Selection)

If the Blank OR Null is not constant part of the selection for all versions of the report, then you made it on Version level RDA Design

I hope, this is a report issue not a Table Conversion.

You can also move all of your selection to the Event Rule part if it fits to your scenario, then you can easily Copy/Paste the AND part for the OR segments.

Hope, this helps a bit.

Regards

Zoltán

 

[deo]

Thanks Zolten,

Yes this is a report and not a table conversion. Your suggestion to move the compare to the report template is what I was planning on doing even though Bill thinks it is foolish and the lazy way out. Thanks for your kind reply!

 

[Adam (DF)]

[ QUOTE ]
Will someone, who comes along after you, know that your logic also excludes or includes null values?


[/ QUOTE ]

This is good to remember. In this case, however, I think you have to make the point based on results, because OneWorld already forces you to use nonsense like "X is equal to <null>" and there's nothing you can do about it.

Testing has shown that "less than or equal to <blank>" finds null values in ER conditional statements, but it does not find null values in data selection.

Further testing has also shown that "is (not) equal to <null>" does not find null values in UBE data selection! So you have to use the ER in that case anyway.

I'm done now. You can all go back to work.

 

[elbyowl]

You were pretty rude to deo, Bill. I don't see how that can help anyone. Anyway, the suggestion to create a selection statement with the multiple and/or combos will make the logic harder to read, not easier. Putting the logic in the code sounds reasonable to me.

 

[Bill Dotson]

My apologies, I didn't mean to be unkind. And I don't think moving the data selection to the report template is lazy or foolish (and that's not what I said) -- it's really the correct thing to do.

But let me explain ...

Given some possible options, it looked like you decided to take the easiest one (changing the data selection to <=blank), even though some noted that might not be correct (but nonetheless might work). To me, that came off as "I don't want to take the time to reproduce those data selection lines," which I (maybe improperly and unkindly) characterized as lazy.

As an alternative, you seemed to indicate that you planned to write code to accomplish the "comparison" (i.e., IF database.column <> NULL), which I characterized as foolish. I still do, but I see from this later response that "comparison" is not what you meant. You really meant "selection."

If you use Zoltan's suggestion, that's data selection -- it doesn't matter whether it comes from the user or is coded into the program. Data selection obtains the desired records at the database level, using SQL, which is mostly very efficient, but event rule code (heck, any code) that filters through an unnecessarily large result set isn't efficient at all.

 

[Chan Rana]

Deo,
I would recommend if you have a NULL values residing in your JDE DB then you fire a database sql to replace them with Blanks this will prevent you from making the selection more complex.
A carefully designed data selection with index can impact a lot on your performance.

My 2c$
Thanks,
Chan

 

[sohamhatkar]

Gene,

the reason your comparison works is that generic JDE tables do not have null values in their columns. One of the layers always ensures that blank or zero (depending on datatype) is written to the table.

Of course its still possible to have null values when updating by using non jde toolset (SQL, etc) or when dealing with foreign tables . . .

A quick observation- We have a 3rd party system inserting data into JDE's Oracle database. This system is writing Null in EDCT and EDSP fields of a custom table. To my surprise Data selection of <=Blank selects Null records in EDSP column. But same data selection on EDCT fails (due to reasons discussed here). May be EDSP being a "character" data type plays a role in this...

 

[Larry_Jones]

Is the third party system actually putting a null in EDSP or is it an empty string?