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

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.

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

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

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

However -

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

does

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

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,

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