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] Developer studio, dealing with nulls/missing in limits

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Developer studio, dealing with nulls/missing in limits
 Login/Join
 
Member
posted
We have just implemnted WebFocus in a test environment and I am learning dev studio. I want a limit that among other things selects gpa_grouping = 'C' or missing and gpa_type = 'O' or missing. In the whereif I have set this up, but it continues to just bring back the gpa_grouping = 'C' and gpa_type = 'O' and drops the records with nulls/missing in these columns. What is the best way to accomplish this. Thank you in advance for your answers.

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


WebFocus 7.7.03, Windows, HTML
 
Posts: 8 | Location: Albuquerque, New Mexico | Registered: May 17, 2012Report This Post
Virtuoso
posted Hide Post
Please post the Where clauses.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
In the Where clause you need to use
WHERE [FIELD] EQ MISSING; or if you are using DEFINE or COMPUTE you need to use MISSING ON condition.

As Gross asked please post your sample code.


WebFOCUS - ver8201
[ReportingServers: Windows 64bit;
Client: tomcat and IIS on windows 2012
AppStudio

 
Posts: 104 | Location: Indianapolis | Registered: November 08, 2007Report This Post
Virtuoso
posted Hide Post
Janice,

How are the fields 'gpa_grouping' and 'gpa_type' defined in your master?
If they do not have the MISSING=ON attribute, you cannot test for missing.

Also (a bit in a nutshell), if you have a FOCUS file or a clustered master for a RDBMS and you are testing whether a field in a child segment has a value or if the child segment is missing, then it is different.
For a FOCUS file you should uset the SET ALL parameter.
For a clustered master you should define the relationship as LEFT OUTER.

Once you have defined the problem for yourself, a little perusing of the manuals is in order.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Janice, in addition to making sure those fields have the MISSING=ON set in the masterfile as others have suggested, it is important to keep in mind that you cannot just mix MISSING with other expressions the way you were describing (assuming your WHERE looks exactly as you mentioned):

quote:
gpa_grouping = 'C' or missing and gpa_type = 'O' or missing


You'd need a WHERE clause that looks somewhat like this:

WHERE (gpa_grouping EQ 'C' OR gpa_grouping EQ MISSING);
WHERE (gpa_type EQ 'O' OR gpa_type EQ MISSING);



Just post your current WHERE clause along with your masterfile field settings for gpa_grouping and gpa_type to better tailor a working sample for you.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
FIELDNAME=GPA_GROUPING, ALIAS=GPA_GROUPING, USAGE=A255, ACTUAL=A255,
MISSING=ON, $
FIELDNAME=GPA_TYPE, ALIAS=GPA_TYPE, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
TABLE FILE ADMISSIONS_APPLICATION_CUNM
PRINT
J0.PERSON_CUNM.PHONE_NUMBER_COMBINED AS 'Phone Number'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_A01 AS 'ACT English'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_A02 AS 'ACT Math'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_A03 AS 'ACT Reading'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_A04 AS 'ACT Science'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_S01 AS 'SAT Verbal'
J3.TEST_HIGHEST_SLOT_VW_CUNM.TEST_SCORE_S02 AS 'SAT Math'
ADMISSIONS_APPLICATION_CUNM.ADMISSIONS_APPLICATION_CUNM.MAJOR AS 'Major'
BY LOWEST ADMISSIONS_APPLICATION_CUNM.ADMISSIONS_APPLICATION_CUNM.ACADEMIC_PERIOD
BY LOWEST J0.PERSON_CUNM.FULL_NAME_LFMI AS 'full Name Lfmi'
BY LOWEST J0.PERSON_CUNM.ID
WHERE ( ADMISSIONS_APPLICATION_CUNM.ADMISSIONS_APPLICATION_CUNM.ACADEMIC_PERIOD EQ '201210' )
AND ( ADMISSIONS_APPLICATION_CUNM.ADMISSIONS_APPLICATION_CUNM.STUDENT_POPULATION EQ 'B' OR 'A' )
AND (( J0.PERSON_CUNM.PRIMARY_ETHNICITY EQ '1' ) OR ( J1.RACE_SLOT_CUNM.AFRICAN_AMERICAN EQ 1 ))
AND (( J2.GPA.GPA_GROUPING EQ 'C' ) OR ( J2.GPA.GPA_GROUPING EQ MISSING ))
AND (( J2.GPA.GPA_TYPE EQ 'O' ) OR ( J2.GPA.GPA_TYPE EQ MISSING ));


WebFocus 7.7.03, Windows, HTML
 
Posts: 8 | Location: Albuquerque, New Mexico | Registered: May 17, 2012Report This Post
Virtuoso
posted Hide Post
From the qualifiers, I gather you are reporting on a join structure.

If the join locates a j2.gpa instance in which GPA_GROUPING is stored as null, your query will retrieve it. But if there simply is no j2.gpa that satisfies the join criterion (for a given instance in the host table), maybe not.

Post the JOINs, any "SET ALL=' statements, and the generated SQL SELECT.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
The join is not dropping the records because they are in the report results until I put in the limites on gpa_type and gpa_grouping. How do you see the generated sql select?


WebFocus 7.7.03, Windows, HTML
 
Posts: 8 | Location: Albuquerque, New Mexico | Registered: May 17, 2012Report This Post
Virtuoso
posted Hide Post
Are the tables FOCUS tables or relational DBMS tables?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
1. Here's a posting of code to generate a SQL statement trace.

2. If all else fails, you can remove those two conditions from the WHERE, and include them in a WHERE TOTAL.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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] Developer studio, dealing with nulls/missing in limits

Copyright © 1996-2020 Information Builders