Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] How to handle NULL values returned by db_lookup function
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How to handle NULL values returned by db_lookup function
 Login/Join
 
Member
posted
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,
 
Posts: 9 | Registered: February 09, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 9 | Registered: February 09, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
You can check for a NULL value with syntax like
IF DB_LOOKUP( ... ) IS MISSING THEN ...
 
Posts: 380 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 103 | Registered: April 27, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] How to handle NULL values returned by db_lookup function

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.