Focal Point
[SOLVED] How to handle NULL values returned by db_lookup function

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/9487003186

March 16, 2016, 12:45 PM
lalvarez13
[SOLVED] How to handle NULL values returned by db_lookup function
Hi.

We would like to know how to handle NULL values returned by the db_lookup function. We have the following expression, but we don't know if there's a polish way to do it:

 IF DB_LOOKUP( dim_producto, PROD_ID, PRODUCTO_ID_NK, PRODUCTO_SK ) GE 1 THEN DB_LOOKUP( dim_producto, PROD_ID, PRODUCTO_ID_NK, PRODUCTO_SK ) ELSE -1 


Any suggestions?

Thanks in advance.

Regards,

Leonardo.

This message has been edited. Last edited by: Tamra,
March 16, 2016, 01:42 PM
Dan Satchell
From the Using Functions reference manual:

If the lookup field has the MISSING=ON attribute in its Master File and the DEFINE or
COMPUTE command specifies MISSING ON, the missing value is returned when the
lookup field is missing. Without MISSING ON in both places, the missing value is converted
to a default value (blank for an alphanumeric field, zero for a numeric field).


WebFOCUS 7.7.05
March 16, 2016, 03:32 PM
lalvarez13
Well, my question concerns when the db_lookup function returns NULL, how should I handle that NULL value (missing value)? I tried to find an NVL function or similar and I didn't. All our Oracle targets don't allow nulls, hence I shouldn't select MISSING option in the master.

Thanks.

Regards,

Leonardo.
March 17, 2016, 10:56 AM
Clif
You can check for a NULL value with syntax like
IF DB_LOOKUP( ... ) IS MISSING THEN ...


N/A
March 17, 2016, 11:04 AM
Kevin W
MISSING is the WebFocus keyword for NULL. But you have to explicitly look for it (MISSING=ON). Otherwise they kindly turn a NULL into a zero or a space. Dan gave you some key information for turning it on so you can use it. Clif told you how to use it.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
March 18, 2016, 01:23 PM
Dan Satchell
If your Oracle targets don't allow nulls (and therefore MISSING isn't ON in the synonym), then the DB_LOOKUP function should return either a value, or a blank (if the field is alphanumeric and no match is found), or a zero (if the field is numeric and no match is found).

For alphanumeric field:
IF DB_LOOKUP(...) EQ '' THEN ....

For a numeric field:
IF DB_LOOKUP(...) EQ 0 THEN ....



WebFOCUS 7.7.05