NULL in Table IO select statement

[johndanter]

Hi folks,

I have an issues that been bugging me.

I'm meant to be fetching values from a table where a certain field is NULL (SS57STYPE ). So I tried using a variable on alias DCTO and set to '' nothing in ER and then used the following select by using less than or equal the variable (which defaults to blank anyway).

SS57STYPE can sometimes be 'WO' or 'WR' or NULL

SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC

No records come back!!!

In the end I solved it with a descending index on SS57STYPE and just got the first record, which happens to be the one I'm after


SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC

How do you guys get around this as normally what I've done works ok. Not sure why it isn't here.
Could it be the nature of DCTO maybe? As ABLK is N which means it'll always add a blank

Thanks

John

 

[craig_welton]

Hi John, technically JDE does not support NULL values in columns. Are you inserting data into that table outside of the toolset?

 

[johndanter]

Hi Craig,

You bet your butt I am. Well, not me, the .NET developers here. I then have to referencing it using my F57MESSS table, which is actually a dropped table view in E1 that points to their .NET table

So, good call

My index idea works, but I'm just confused

 

[johndanter]

Maybe if I changed the table column SS57STYPE to allow blank values, then E1 wouldn't be adding the ' ' for me.

As even when I use a different alias in the select (with ALBK = Y) E1 is still creating this

SELECT * FROM CRPDTA.F57MESSS WHERE ( SS57SKEY1 = 'SAMPLEPLAN' AND SS57SADD = '158527' AND SS57SBRAN = '728' AND SS57STYPE <= ' ' AND SS57SITEM = '4826096' ) ORDER BY SS57SKEY1 ASC,SS57SADD ASC,SS57SBRAN ASC

But I am confused, and NULL is less than blank. So this should be working

 

[BOster]

Did I understand your question correctly - you want IS NULL in the WHERE clause? If so, since you are on TR9.1.5.3 the JDEBASE API added support for NULL in the WHERE clause. Look at the JD_Edwards_EnterpriseOne_In-Memory_Tools_C_API_Reference.pdf on Oracle's site if you can find it (sorry don't have doc link).

From the guide:

IS NULL or IS NOT NULL
Create a WHERE clause testing for NULL or IS NOT NULL by passing lpValue as NULL and nValues as 1. When you do this, the only valid values for nCmp are JDEDB_CMP_EQ and JDEDB_CMP_NE. 29 | JD EDWARDS IN-MEMORY TOOLS C API REFERENCE
/* Compare F0011.ICICU IS NULL */
jdeNIDcpy(dsSelect[0].Item1.szDict, NID_ICU);
jdeNIDcpy(dsSelect[0].Item1.szTable, _J("F0011"));
dsSelect[0].Item1.idInstance = 0;
dsSelect[0].lpValue = NULL;
dsSelect[0].nValues = 1;
dsSelect[0].nAndOr = JDEDB_ANDOR_AND;
dsSelect[0].nCmp = JDEDB_CMP_EQ;

 

[craig_welton]

Thanks Brian. I wonder if that's only available through JDEBase C API, meaning you can't control that in ER?

John, I don't believe NULL is <' '. NULL is NULL which is why there is IS NULL and IS NOT NULL. If the .NET folks insert blanks into that column rather than leaving it NULL (or you COALESCE a ' ' in your SQL view), I believe it will work as you are expecting.

Craig

 

[johndanter]

Thanks guys.

Yeah I didn't want to address this in C if I could help it Brian. It'as just a simple table IO command and I'm disillusioned E1 can't handle it.
I found the doc Brian, thanks. This one?
https://support.oracle.com/epmos/ma...ocessor?attachid=1952465.1:CAPI&action=inline

You are right about the .NET team inserting blanks as i got them to update them via SQL and it worked like a treat. Sadly, any extra records created going forward won't have blanks. Hence the post here.

My index idea seems to be working fine. So I'll run with that.
I guess that's the same as the COALESCE bit but in reverse...in a way? In that it brings back the NULL values first

Silly IT stuff

 

[craig_welton]

With COALESCE you can substitute the column value in the case of a NULL. So when your trouble column is referenced in the view SQL source, it would say COALESCE(column, ' ') as SS57STYPE. Then SS57STYPE in the view results would be blank or have the actual value if not NULL.

 

[BOster]

I wonder if that's only available through JDEBase C API, meaning you can't control that in ER?

Craig

Don't know, I have never looked for it in ER code. If not, I would think they could easily make this work from ER code as well.

 

[Larry_Jones]

...
But I am confused, and NULL is less than blank. So this should be working

Your confusion is believing that NULL is less than blank. Its not. NULL has no value.

 

[johndanter]

Your confusion is believing that NULL is less than blank. Its not. NULL has no value.

Well that's just plain childish of them Larry

Craig, are you saying that in my dropped table view, I should be using COALESCE(column, ' ') as SS57STYPE?
If so I may as well do this on all the other columns, just in case of future checks. No?

Thanks for your help guys

 

[craig_welton]

Sure, to be safe. But the substitution value changes based on the data type of the column, of course.

 

[johndanter]

Ok thanks guys

 

[johndanter]

Reawakening this thread for my own use and future developers.

The fields I need to check are NULL are strings in the table and performing any kid of = BLANK or = unassigned variable never hits a record...as the fields are NULL (external table)

So what I did was this and it works....very consuming, but it works.
Issue is the database value is not blank, it's null and Table IO doesn't allow you to do tat. The minute you use an unassigned variable it adds a blank

Same too can be said of the fetch next assingnments

Form: Fix/Inspect - W55JDTESB
Object: FC F57MESSS
Event: Button Clicked
Event GUID: 43bf9054-de96-4c61-a961-3d6e6498d3f6
----------------------------------------
Event Level Variables
----------------------------------------
evt_GTSSysKey157SKEY1 [57SKEY1]
evt_GTSSysAddress57SADD [57SADD]
evt_GTSSysBranch57SBRAN [57SBRAN]
evt_GTSSysItemTyp57SITYP [57SITYP]
evt_GTSSysItem57SITEM [57SITEM]
evt_GTSSysFamily57SFAM [57SFAM]
evt_GTSSysType57STYPE [57STYPE]
evt_GTSSysDesc57SDESC [57SDESC]
----------------------------------------
00001 //
00002 VA evt_GTSSysKey157SKEY1 [57SKEY1] = "SAMPLEPLAN"
00003 VA evt_GTSSysAddress57SADD [57SADD] = "167044"
00004 VA evt_GTSSysBranch57SBRAN [57SBRAN] = "211"
00005 //
00006 VA evt_GTSSysItem57SITEM [57SITEM] = <NULL>
00007 VA evt_GTSSysItemTyp57SITYP [57SITYP] = <NULL>
00008 VA evt_GTSSysFamily57SFAM [57SFAM] = <NULL>
00009 VA evt_GTSSysType57STYPE [57STYPE] = <NULL>
00010 VA evt_GTSSysDesc57SDESC [57SDESC] = <NULL>
00011 //
00012 //
00013 //

00014 -F57MESSS.Select [Index 4: KEY1, ADDRESS, BRANCH]
VA evt_GTSSysKey157SKEY1 [57SKEY1] = TK Sys Setting Key 1 [57SKEY1]
VA evt_GTSSysAddress57SADD [57SADD] = TK Sys Setting Address [57SADD]
VA evt_GTSSysBranch57SBRAN [57SBRAN] = TK Sys Branch [57SBRAN]

00015 -F57MESSS.FetchNext [Index 4: KEY1, ADDRESS, BRANCH]
FC System Setting ID 57SYSID [57SYSID] <- TK System Setting ID [57SYSID]
VA evt_GTSSysType57STYPE [57STYPE] <- TK Sys Setting Type [57STYPE]
VA evt_GTSSysFamily57SFAM [57SFAM] <- TK Sys Setting Family [57SFAM]
VA evt_GTSSysItemTyp57SITYP [57SITYP] <- TK Sys Setting Item Type [57SITYP]
VA evt_GTSSysItem57SITEM [57SITEM] <- TK Sys Setting Item [57SITEM]
00016 -While SV File_IO_Status is equal to CO SUCCESS

00017 | -If VA evt_GTSSysItem57SITEM [57SITEM] is equal to <Blank>
| And VA evt_GTSSysItemTyp57SITYP [57SITYP] is equal to <Blank>
| And VA evt_GTSSysFamily57SFAM [57SFAM] is equal to <Blank>
| And VA evt_GTSSysType57STYPE [57STYPE] is equal to <Blank>

00018 | | -F57MESSV.FetchSingle [Index 2: SYSSETTING_SETTING_NIX]
| | FC System Setting ID 57SYSID [57SYSID] = TK Sys Setting [57SYSSET]
| | FC Sys Val 1 57SVAL1 [57SVAL1] <- TK Sys Setting Value 1 [57SVAL1]

00019 | End If

00020 | -F57MESSS.FetchNext [Index 4: KEY1, ADDRESS, BRANCH]
| FC System Setting ID 57SYSID [57SYSID] <- TK System Setting ID [57SYSID]
| VA evt_GTSSysType57STYPE [57STYPE] <- TK Sys Setting Type [57STYPE]
| VA evt_GTSSysFamily57SFAM [57SFAM] <- TK Sys Setting Family [57SFAM]
| VA evt_GTSSysItemTyp57SITYP [57SITYP] <- TK Sys Setting Item Type [57SITYP]
| VA evt_GTSSysItem57SITEM [57SITEM] <- TK Sys Setting Item [57SITEM]

00021 End While

 

[Vadim]

In-Memory_Tools_C_API

Hi Brain,
I need this JD_Edwards_EnterpriseOne_In-Memory_Tools_C_API_Reference.pdf, but I don't have Oracle Support ID, because I'm freelancer. Could you help me?

Vadim