Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Filter: Not Equal to
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Filter: Not Equal to
 Login/Join
 
Member
posted
Hello,

I am using Not Equal to filter to filter out some product names.

Ex: App_Prod_Name NE 'ABC' or 'DEF' or 'XYZ'

Doing this also filters out rows where App_Prod_Name is blank. I want the blanks to be included.. Is there a reason why this is happening?

Thank you,
Gagan

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 23 | Registered: September 05, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
quote:
Ex: App_Prod_Name NE 'ABC' or 'DEF' or 'XYZ'


First off, the 'or' should be capitalized in your code above
 App_Prod_Name NE 'ABC' OR 'DEF' OR 'XYZ' 


Second - What do you mean by blank? An empty string? Or MISSING (NULL) values?


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 281 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
Seems to work fine:
 
DEFINE FILE CAR
TEST/A20  = IF COUNTRY EQ 'JAPAN' THEN '' ELSE COUNTRY;
-*TEST/A20 MISSING ON = IF COUNTRY EQ 'JAPAN' THEN MISSING ELSE COUNTRY;
END

TABLE FILE CAR
PRINT TEST COUNTRY
WHERE TEST NE 'ENGLAND' OR 'ITALY'
END 


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 368 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Second - What do you mean by blank? An empty string? Or MISSING (NULL) values?


Here is the exact code that I have:

WHERE APPorDB_Prod_name NE 'IaaS Internal' OR 'SITECORE' OR 'Sitecore';

And yes, by blank I mean NULL Values

The code should not disregard NULL values, I want to see those in my report.


WebFOCUS 8
Windows, All Outputs
 
Posts: 23 | Registered: September 05, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Could you do a count of your records with the exact same WHERE conditions except for APPorDB_Prod_name? For that WHERE test get the count WHERE APPorDB_Prod_name EQ MISSING; and then the same count WHERE APPorDB_Prod_name NE MISSING;


WebFOCUS 8204, Unix, Windows
 
Posts: 1522 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by BabakNYC:
Could you do a count of your records with the exact same WHERE conditions except for APPorDB_Prod_name? For that WHERE test get the count WHERE APPorDB_Prod_name EQ MISSING; and then the same count WHERE APPorDB_Prod_name NE MISSING;



COUNT: WHERE APPorDB_Prod_name EQ MISSING = 60 records
COUNT: WHERE APPorDB_Prod_name NE MISSING = 1215 records
COUNT: WHERE APPorDB_Prod_name NE 'IaaS Internal' OR 'SITECORE' OR 'Sitecore' = 1179 records
COUNT: WHERE APPorDB_Prod_name EQ 'IaaS Internal' OR 'SITECORE' OR 'Sitecore' = 36 records

Does this help?


WebFOCUS 8
Windows, All Outputs
 
Posts: 23 | Registered: September 05, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yes. This establishes that you do in fact have missing values and WebFOCUS can find them. Do you see the null values if you only filtered on 'SITECORE'? Could you show us a simplified version of your report? Null values aren't in your WHERE test so, I'd expect them to stay in the answer set. Is APPorDB_Prod_name a real field or a DEFINE? Is there a JOIN present? If this is an RDBMS what does the SQL Trace show? Something else must be filtering the null values out.


WebFOCUS 8204, Unix, Windows
 
Posts: 1522 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
This also depends on if you are joining table and if so what the value of "ALL" is set to.




Scott

 
Posts: 862 | Registered: May 24, 2004Reply With QuoteReport This Post
Master
posted Hide Post
Are the requirements something like: Show all Country's except 'France' and 'England' and show row if Country is NULL...and you are reading a relational table?

If so try something like this:
TABLE FILE CAR
PRINT CAR 
BY COUNTRY
WHERE NOT COUNTRY IN ('FRANCE','ENGLAND')
   OR COUNTRY IS MISSING;
END  

*IF* the sample CAR file was a relational table, the WHERE would translate into *something like* this:
WHERE ((T1."COUNTRY" NOT IN('FRANCE', 'ENGLAND')) 
OR    (T1."COUNTRY" IS NULL));  
 
Posts: 754 | Registered: April 23, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
If there is a join involved, try including the following set
 SET SHORTPATH = SQL 
That will supply missing values for the fields in a missing cross-referenced segment in an outer join. Applies screening conditions against this record and retains the record on the report output if it passes the screening test.

Docs here


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 281 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Master
posted Hide Post
quote:
If there is a join involved...

Yep, looking at the column name being filtered - App_Prod_Name - it is possible that this column is in a child lookup segment, and hence SET SHORTPATH = SQL should be considered.

This is one of the fun parts of IT - conjecturing. :-) https://en.wikipedia.org/wiki/Conjecture
 
Posts: 754 | Registered: April 23, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Filter: Not Equal to

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.