Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


John Watkins

Recommended Posts

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);

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...