[SOLVED] How to handle NULL values returned by db_lookup function
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
Thanks in advance.
Leonardo.This message has been edited. Last edited by: Tamra,
March 16, 2016, 01:42 PM
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).
March 16, 2016, 03:32 PM
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.
March 17, 2016, 10:56 AM
You can check for a NULL value with syntax like IF DB_LOOKUP( ... ) IS MISSING THEN ...
March 17, 2016, 11:04 AM
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
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).