Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Filter: Not Equal to
 Login/Join
 
Gagan Marwah
Silver 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: 29 | Registered: September 05, 2017Report This Post
Hallway
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Frans
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: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Gagan Marwah
Silver 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: 29 | Registered: September 05, 2017Report This Post
BabakNYC
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gagan Marwah
Silver 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: 29 | Registered: September 05, 2017Report This Post
BabakNYC
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
TexasStingray
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: 865 | Registered: May 24, 2004Report This Post
David Briars
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: 822 | Registered: April 23, 2003Report This Post
Hallway
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
David Briars
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: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders