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, GaganThis message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8 Windows, All Outputs
February 13, 2019, 12:23 PM
Hallway
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:
February 13, 2019, 12:56 PM
Frans
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
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
February 13, 2019, 01:32 PM
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;
WebFOCUS 8206, Unix, Windows
February 13, 2019, 02:08 PM
Gagan Marwah
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
February 13, 2019, 02:18 PM
BabakNYC
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
February 13, 2019, 02:51 PM
TexasStingray
This also depends on if you are joining table and if so what the value of "ALL" is set to.
Scott
February 13, 2019, 03:40 PM
David Briars
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));
February 13, 2019, 04:51 PM
Hallway
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.
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.
