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     [SOLVED] Comparing MISSING with FOR ALL NEXT using a Variable Problem - Maintain

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Comparing MISSING with FOR ALL NEXT using a Variable Problem - Maintain
 Login/Join
 
RichardA
Gold member
posted
When doing the following:

For All Next
FACILITY.FACILITY.FACILITY_SK
Into
testStk
Where
(FACILITY.FACILITY.FACILITY_ADDR_DIRECTION EQ MISSING);

I get the right amount of records back in the stack.



If I compare it using a variable:

Compute testvar / A1 MISSING ON = MISSING;

For All Next
FACILITY.FACILITY.FACILITY_SK
Into
testStk
Where
(FACILITY.FACILITY.FACILITY_ADDR_DIRECTION EQ testvar);

I get 0 records. The database is a Microsoft SQL Server 2008 R2 database.

Why is this happening?

This message has been edited. Last edited by: RichardA,


Sandbox Version: 8.1.04
Sandbox Platform: Windows 2008 R2
Testing Version: 7.7.02M
Testing Platform: Windows Server 2008 R2
WF Production Version: 7.7.02M
Production Platform: Windows Server 2008
 
Posts: 57 | Location: Philadelphia, PA | Registered: June 19, 2007Report This Post
Maintain Wizard
Master
posted Hide Post
I am honestly not sure why it is happening either. It does work with FOCUS files, but when I go to SQL files I see what you are seeing. Using MISSING or . in the WHERE clause works, but assigning a variable to that value fails.

If this is a problem for you please open a case with Customer Support and we can get it to programming.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
GamP
Virtuoso
posted Hide Post
Could it be that the missing value for an internal compute variable (with where eq field) differs from the value used when connecting directly to the dbms (with the where .. eq missing)?


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Tewy
Platinum Member
posted Hide Post
You can't compare a null value directly to another null value.

Your first piece of code is testing using the database equivalent of fieldname IS NULL, the second is saying where field1 = field2, and two nulls are not equal.

It appears from the microsoft website there is a database setting you can use to control this behaviour:

quote:
Comparing Null Values
Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Use the IS NULL or IS NOT NULL clauses to test for a NULL value.

....

With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL



WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
RichardA
Gold member
posted Hide Post
Thanks, Tewy. It looks like I'll have to find another way around this.

I can't directly compare the field to missing because I don't know whether it's missing or not. Also the where in the FOR n NEXT was a compound one in the original code. I was comparing about 7 or 8 fields to their maintain stack variable counterparts. It looks like I'll have to separate out the fields that can be NULL and test those separately copying and filtering the results into another stack.

Thanks again.

quote:
Originally posted by Tewy:
You can't compare a null value directly to another null value.

Your first piece of code is testing using the database equivalent of fieldname IS NULL, the second is saying where field1 = field2, and two nulls are not equal.

It appears from the microsoft website there is a database setting you can use to control this behaviour:

quote:
Comparing Null Values
Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Use the IS NULL or IS NOT NULL clauses to test for a NULL value.

....

With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL



Sandbox Version: 8.1.04
Sandbox Platform: Windows 2008 R2
Testing Version: 7.7.02M
Testing Platform: Windows Server 2008 R2
WF Production Version: 7.7.02M
Production Platform: Windows Server 2008
 
Posts: 57 | Location: Philadelphia, PA | Registered: June 19, 2007Report 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     [SOLVED] Comparing MISSING with FOR ALL NEXT using a Variable Problem - Maintain

Copyright © 1996-2020 Information Builders