Is there a SQL setting to cause the DB to pass back records ...

[NYCBabak .]

What DBMS are you using i just tested this in SQL Server and its working without the extra where test.

TABLE FILE ORDERS

PRINT *

WHERE SHIPREGION IS MISSING

END

SELECT

T1.OrderID,

T1.CustomerID,

T1.EmployeeID,

T1.OrderDate,

T1.RequiredDate,

T1.ShippedDate,

T1.ShipVia,

T1.Freight,

T1.ShipName,

T1.ShipAddress,

T1.ShipCity,

T1.ShipRegion,

T1.ShipPostalCode,

T1.ShipCountry,

T1.ORD_DT

FROM

Northwind_new.dbo.Orders T1

WHERE

(T1.ShipRegion IS NULL);

[David Briars]

Are you JOINing two relational tables (outer join)

Are you testing on a column in the cross-referenced segment (child table)

If so, take a look at the SHORTPATH setting.

[John Watkins]

I am using Microsot SQL Server 2016 on windows.

[John Watkins]

No. Just a simple query from 1 SQL table. If I add the OR TESTFLD IS MISSING I get the record with the NULL value, take out the OR test and I get nothing.

[NYCBabak .]

Turn on the SQL trace with the TABLE request testing for missing. What does the SQL say

Add this to the beginning of your request. Itll tell you what WebFOCUS is telling SQL Server. You shouldnt need the extra WHERE test as I stated in my simple example, WHERE SHIPREGION IS MISSING translates to WHERE ShipRegion IS NULL;

SET XRETRIEVAL=OFF

SET EMPTYREPORT=OFF

SET TRACEON = STMTRACE//CLIENT

SET TRACEON = STMTRACE/2/CLIENT

SET TRACESTAMP = OFF

SET TRACEWRAP = 132

SET TRACEUSER = ON

[John Watkins]

No joins involved in this test.

[John Watkins]

This:

SET XRETRIEVAL=OFF

SET EMPTYREPORT=OFF

SET TRACEON = STMTRACE//CLIENT

SET TRACEON = STMTRACE/2/CLIENT

SET TRACESTAMP = OFF

SET TRACEWRAP = 132

SET TRACEUSER = ONTABLE FILE FRPAIR

TABLE FILE FRPAIR

PRINT ACCT USERDEF

WHERE ACCT EQ JOJOJJJJRNNFJO

-*WHERE USERDEF NE X OR USERDEF IS MISSING

WHERE USERDEF NE X

END

Yields this:

SELECT T1.ACCT, T1.USERDEF FROM FRPAIR T1 WHERE (T1.USERDEF <> X) AND (T1.ACCT = JOJOJJJJRNNFJO);

And the other WHERE test that includes OR USERDEF IS MISSING yields this:

SELECT T1.ACCT, T1.USERDEF FROM FRPAIR T1 WHERE ((T1.USERDEF <> X) OR (T1.USERDEF IS NULL)) AND (T1.ACCT = JOJOJJJJRNNFJO);

But why does SQL need the implicit OR value is NULL statement The same code running on a focus file, or flat file, or a flat file joined to a SQL table doesnt require it. Ill have to check with our DBA and see if it is a SQL setting.

[John Watkins]

Is there a SQL setting to cause the DB to pass back records with a NULL value when using the NE operator without having to add a specific test for OR FIELD IS MISSING

Example:

TABLE FILE TESTDB

PRINT ACCOUNT TSTFLD

WHERE ACCOUNTT EQ TESTACCOUNT

-*WHERE TESTFLD NE X OR TSTFLD IS MISSING

WHERE TSTFLD NE X

END

If dont specify OR TSTFLD IS MISSING you wont get any records back.

But if your TESTDB was a focus file you wont have to add the OR test.

Even if you create a focus file with all the ACCOUNTs in it and then JOIN to the TESTDB you still dont have to have the OR test. Only when going directly at the SQL table you do need the OR test.

[Alban Hertroys]

The core of the problem is that (in SQL) NULL means that the value is not known, hence it is not possible to say whether it is equal or not equal to some given value. When NULL is involved such expressions, the result of the expression is also NULL (unknown). A boolean expression can thus return 3 different values: true, false or NULL. Thats SQLs 3-values logic, it has a (much debated) purpose.

However, WHERE-clauses are expected to result in either true or false. If a WHERE expression yields NULL, the WHERE clause is considered to yield `false.

And thats why you should write WHERE TESTFLD NE X OR TESTFLD IS MISSING;

WHERE TESTFLD NE X yields NULL when TESTFLD is NULL and hence the WHERE-clause yields false where you want it to yield true.

The full expression works, because NULL OR TRUE yields true.

OTOH, if you were instead to check for WHERE TESTFLD EQ X, then you could omit the MISSING part as the NULL result of the expression would cause WHERE to yield false.

Now there are some RDBMSs (Im looking at you, Oracle!) that, for historical reasons, treat equality on NULL values as false, or consider empty strings and NULL values to be the same thing. I wouldnt rely on that behaviour, they may fix it at some point.

BTW, youre supposed to end WHERE-clauses in FOCUS with a semi-colon. You can omit it on the last clause, but that will bite you sooner rather than later.

[John Watkins]

Very interesting. Thank you for the explanation. Makes sense I suppose. I wonder why IBI doesnt do the same thing

The same code yields different results on other file types.

[Toby Mills]

Hi John

Quick question - does your Master file for the MSSQL data have the MISSING attribute on your fields that youre looking it

Just wondering. It does seem thick to have to add the OR to catch your MISSING records. Seems logically like those null values are NE X - so those records should return. Unless WF thinks it needs to evaluate things to blank or 0 (that would mean youre missing your MISSING).

I mostly do admin duty these days so I found it educational to read up on the Missing data in the command language reference manual. Lots of new (to me) settings to see in chapter 13 -Handling Records With Missing Field

Values in the 8207 manual.

Thanks!

Toby

[John Watkins]

Toby, yes. It does have the MISSING attribute for the fields in question. I was mainly concerned that the same code works different for FOCUS files and HOLD files vs a SQL Table.

[Toby Mills]

Thanks John.

That is interesting that it acts differently. Ive seen this before but were talking way back in the mid 90s and that was around behavior of the SET ALL parameter.

A new parameter (to me anyway) that caught my attention was

SET MISSINGTEST = {NEW|OLD|SPECIAL}

This shouldnt matter in the context of a WHERE test, but once upon a time, someone told me WHERE tests are essentially handled at the same time as DEFINEs. The default now is NEW where it used to be OLD. But really - I dont think this is your deal. It might be worth a SET ALL just to see your settings. Also a ENGINE SQLMSS just to see those settings.

Heres a good question - if you copy/paste your SQL into SQL Server Management Studio with just the TESTFLD NE X (no mention of missing), what does SQL Server do Does it return the rows with missing normally if It does - then WebFOCUS is filtering that result down more post-data retrieval. That gives clues.

If SQL Server does not give you records back, it might be worth checking SQL Servers ANSI_NULLS setting.

SET ANSI_NULLS (Transact-SQL)

They reference a short script to see how your ANSI_NULLS is set:

DECLARE @ANSI_NULLS VARCHAR(3) = OFF;

IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = ON;

SELECT @ANSI_NULLS AS ANSI_NULLS;

Mine is ON for what thats worth.

I think youll be a step closer to understanding it by trying the query (without mentioning the null) and seeing what comes back.

This becomes an academic discussion after a while - the main thing is that you have it working of course. Im just curious how this is happening also.

Toby

[John Watkins]

Interesting Toby. I have not seen the MISSINGTEST either. Ill have to do some research on that. I tried the setting on a SQL table and a FOCUS table joined to a SQL table, but the results were the same. I tried various SQLMSS settings as well. I was hoping the SQL SQLMSS SET OPTIMIZATION NOAGGR might yield different results, but it was the same. Thanks for your help!