Can someone please help me? I have joined two focus files and now want to extract data with parameters. I am trying to include a where clause that doesn't bring back null values for a particular field. In dev studio/report painter I have used the condition where field is not missing but it doesn't work.

Since this is a focus file....do I need to enter this condition from the text editor. If so what is the command. Do I have to prefix the field name with the file name?

HELP!!!!!

Also, I tried pulling the same query by joining two oracle tables and it didn't work which is why i decided to create focus files. Here's the code from the oracle tables:
TABLE FILE SAVADMT
SUM
SAVADMT_TERM_CODE
SAVADMT_FULL_NAME
SAVADMT_STYP_CODE
SLRRASG_BLDG_CODE
SLRRASG_ROOM_NUMBER
BY SAVADMT_PIDM
HEADING
""
FOOTING
""
WHERE SAVADMT_TERM_CODE EQ '200809';
WHERE SAVADMT_APDC_CODE EQ 'PC' OR 'AC';
WHERE SLRRASG_BLDG_CODE NE MISSING;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

That should work for either Oracle tables or FOCUS databases.

I usually code it as:

WHERE column-name IS-NOT MISSING

Are you sure that the column has null values in it, or is it blank?

You could try:

WHERE (column-name IS-NOT MISSING) OR (NOT column-name EQ '');

Thanks so much Francis...you are right....the field was not null but blank. I used the code for blank and it worked. Thanks again!!!

You're welcome, kitten!

Thank you, Francis Mariani (and everyone else) !

I just got into the forum and wanted to say what you said, and it deserves repeating :

Remember that "nulls propagate" and can nullify (pun intended) the entire statement, so one must be careful using them.

"Missing" ("Null") is not the same as "" (empty string), since null means there is NO value, whereas "" is a value

The value "" is much the same as zero (0) is numerically.

"Missing" in WF seems to occur most naturally as a "short path" meaning that a join results in a "no match" condition for the JOIN in the related dataset.

An "inner join" (the default) naturally returns ONLY those records that match in BOTH datasets, and would avoid the problem of removing them from the answer set.

On the other side of the issue, the "missing" condition can be used, for example, to find all customers who have no orders (to delete them, or to send them a catalog ?)

Thanks for the insight everyone gave on this intriguing issue !

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