NYCBabak . Posted December 14, 2020 Share Posted December 14, 2020 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 More sharing options...
David Briars Posted December 14, 2020 Share Posted December 14, 2020 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. Link to comment Share on other sites More sharing options...
John Watkins Posted December 14, 2020 Author Share Posted December 14, 2020 I am using Microsot SQL Server 2016 on windows. Link to comment Share on other sites More sharing options...
John Watkins Posted December 14, 2020 Author Share Posted December 14, 2020 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. Link to comment Share on other sites More sharing options...
NYCBabak . Posted December 14, 2020 Share Posted December 14, 2020 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 More sharing options...
John Watkins Posted December 14, 2020 Author Share Posted December 14, 2020 No joins involved in this test. Link to comment Share on other sites More sharing options...
John Watkins Posted December 14, 2020 Author Share Posted December 14, 2020 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 More sharing options...
John Watkins Posted December 14, 2020 Author Share Posted December 14, 2020 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 More sharing options...
Alban Hertroys Posted December 15, 2020 Share Posted December 15, 2020 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 More sharing options...
John Watkins Posted December 15, 2020 Author Share Posted December 15, 2020 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. Link to comment Share on other sites More sharing options...
Toby Mills Posted December 16, 2020 Share Posted December 16, 2020 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 More sharing options...
John Watkins Posted December 17, 2020 Author Share Posted December 17, 2020 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. Link to comment Share on other sites More sharing options...
Toby Mills Posted December 17, 2020 Share Posted December 17, 2020 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 More sharing options...
John Watkins Posted December 17, 2020 Author Share Posted December 17, 2020 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now