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,

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.

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!


T

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

T

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