MISSING doesn't work with COMPUTE ?

The follg code is printing '200' for all rows immaterial of value of J missing or not. Any suggestions ? I tried making J as 'MISSING=ON/OFF' in master file. No improvement.

TABLE FILE TT
PRINT
COMPUTE K/I11 = IF J EQ MISSING THEN 100 ELSE 200;
END

Piipster

Guru

posted

Hide Post

Hi JohnE.

What is your datasource?
Are null values allowed for field J?

The MISSING ON/OFF is not something that you can just apply. If your data is from an RDBMS when you generate the master file it will mark whether the fields allow nulls or not based on the definition in the rdbms.

ttfn, kp

Access to most releases from R52x, on multiple platforms.

Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003

Ignored post by Piipster posted

Show Post

<JohnE>

posted

I am using a DB2 table to get the data. The J is nullable in the table. The table has 5 rows with J as NULL and 5 rows with some integer value. The master file was generated using 'Create Synonym' from the Webfocus Dev Studio and it seems to be working corrrectly (meaning assigning MISSING=OFF for non-nullable fields and MISSING=ON for nullable fields). The condition even works when used in a WHERE clause as follows. It just does not seem to work with a 'IF Statement'.

TABLE FILE TT
PRINT J
WHERE J NE MISSING;
-* WHERE J EQ MISSING;
-* WHERE J IS MISSING;
-* WHERE J IS NOT MISSING;
END

Tony A

Expert

posted

Hide Post

John,

Be very careful when refering to an IF in a predicate and an IF as a function (e.g. in a compute). They do not necessarily behave in the same manner.

To my knowledge, you can use IF or WHERE with the operator of IS MISSING, IS-NOT MISSING etc. but not when using it in a function e.g.
COMPUTE somefield/format = IF function THEN .....

I don't have time at present to test this but have you thought about using boolean logic?

COMPUTE K/I11 = ((J EQ MISSING) * 100) + ((J NE MISSING) * 200);

Which should give you a value of 100 when J is NULL otherwise a value of 200.

It's worth a try at least.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

Tony A

Expert

posted

Hide Post

...... which doesn't work.

However -

COMPUTE K/I11 = ((J EQ '') * 100) + ((J NE '') * 200);

does

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

<JohnE>

posted

Sorry, I had forgotton to mention ... the reason why I was using "J EQ MISSING" instead of "J EQ ''" is because type of J is datetime. It had given me follg error. Any idea of how to make the condition work for null datetimes ?

0 ERROR AT OR NEAR LINE 6 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Dharma

Silver Member

posted

Hide Post

Will converting the date/time field into alphanumeric and checking with this converted field wont work?

J_NEW/A20 = HCNVRT(J, '(HYYMDS)', 20, A20);

COMPUTE K/I11 = ((J_NEW EQ '') * 100) + ((J_NEW NE '') * 200);

Regards,
Dharma

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

Posts: 41 | Location: Boston | Registered: August 17, 2005

Ignored post by Dharma posted

Show Post

<JohnE>

posted

I didn't think of that! It worked. Thx.

Please Wait. Your request is being processed...