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  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Utilizing DB_LOOKUP
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Utilizing DB_LOOKUP
 Login/Join
 
Member
posted
How can I use DB_LOOKUP? I can't quite figure out the documentation behind it and am very new to using WEBfocus. Basically, I have a table named CustomerRegistration with a CustomerID in it. I want to have the function look to see if there are any other records with the same CustomerID in it (every time a customer interacts with our platform, we create a record of it in this table).

If there is only a single instance of the CustomerID being in the table, return null or something that notes it only appears once so I can filter the report based on that (meaning this is the first time we have had this customer on our platform).

All I've found thus far is this...

DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld);
where:

look_mf
Is the lookup Master File.

srcfld1, srcfld2 ...
Are fields from the source file used to locate a matching record in the lookup file.

lookfld1, lookfld2 ...
Are columns from the lookup file that share values with the source fields. Only columns in the table or file can be used; columns created with DEFINE cannot be used. For multi-segment synonyms, only columns in the top segment can be used.

returnfld
Is the name of a column in the lookup file whose value is returned from the matching lookup record. Only columns in the table or file can be used; columns created with DEFINE cannot be used.

But don't completely understand it in my context.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: December 13, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Does something such as this may work for you ?

TABLE FILE RegistrationTbl
SUM CNT.DST.RegistrationID NOPRINT
BY CustomerID
WHERE TOTAL CNT.DST.RegistrationID GT 1;
-*ON TABLE HOLD AS CustNbRegist
END


Assuming that in your table you have a unique registration number, then by doing a count per Customer of how many registration they have, you can then filter those with less than 1 registration.
This will result in a report that show only customer with more than 1 registration. You can hold the result in a file if you prefer.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2296 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
There are several ways to us the DB_LOOKUP function. If this is a follow up post from another one that must work with BUE so you do not have access to the code below is an example. For the example the assumptions are:

1. The table you want to see if the customer id is in the call customers.
2. Both table's have a column called customerid with the same format usage format in this case I9.

we will create 2 columns one for the lookup and one to evaluate the lookup. I will use computes as apposed to defines as there may be less records return but you can use a define as well.

COMPUTE DBL_CUSTOMERID/I9 = DB_LOOKUP(CUSTOMERS, customerid, customerid, customerid); NOPRINT
COMPUTE NEW_CUSTROMER/A1 = IF DBL_CUSTOMERID EQ 0 THEN 'Y' ELSE 'N'; 


The third customerid in the call to the DB_LOOKUP function is any column you want to return from the CUSTOMERS table. I chose the same column as it is an integer, the value if it does not exist is a 0 as apposed to a blank or null for an alpha column.

Hope this helps,




Scott

 
Posts: 864 | Registered: May 24, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Think of it as being similar to the result of the following subselect within SQL

select ....
      , (select returnfld from look_mf S1
          where T1.srcfld1 = S1.lookfld1
            and T1.srcfld2 = S1.lookfld2
            ...) as returnfld
  from srctable T1

You've probably realised at this point that this is not exactly what you are needing!


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: 5661 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Just needed to check first Smiler

You might like to try DB_EXPR, which allows you to introduce an SQL expression into your code.

You will need to capture an SQL trace to ensure that you get the predicate tables correct. Examples throughout this forum.

TABLE FILE WF_RETAIL_CUSTOMER
SUM ID_INCOME
    COMPUTE CNT_CUST/I11 = DB_EXPR(select count(ID_CUSTOMER) from _wf_retail_sales S1 where S1.ID_CUSTOMER = T1.ID_CUSTOMER); AS 'Count'
BY ID_CUSTOMER
WHERE RECORDLIMIT EQ 100
END


T

This message has been edited. Last edited by: Tony A,



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: 5661 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Utilizing DB_LOOKUP

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