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);
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.
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,
Windows, All Outputs
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
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,
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!
Just needed to check first
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
TThis message has been edited. Last edited by: Tony A,
|Powered by Social Strata|