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,

Please post the Where clauses.

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.

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.

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.

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 ));

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.

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?

Are the tables FOCUS tables or relational DBMS tables?

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.