I have a table on my system that stores different type of customer information in a single field:

Customer # A10
Customer Chain # A5
Customer Type # A3

The field is A10, the above are stored right justified in the field, and there is another field that tells the applications what type of data is stored in that field. For Example: "C" = Customer #, "H" = Customer Chain, "T" = Customer Type.

I have 3 lookup tables to get the description of the field, i need a technique to join this file to the three different tables based on the field that definenes what's stored in it.

Example.

Field A Field B
---------- -------
1234567890 C <----- Get description from the customer table
12345 H <----- Need a define to change this to A5 then get description from the customer chain table
123 T <----- Need a define to change this to A3 then get description from the customer type table

Hope I explained my predicament well, I am fairly new with WebFocus development.


Thanks,
Joe Beydoun
Lipari Foods
v. 7.6.10
DB2 database

This message has been edited. Last edited by: Joe Beydoun,

Try something like this:

JOIN CUSTOMER   IN TABLE1 TO CUSTOMER   IN TABLE_CUST_DESC  AS J1
JOIN CUST_CHAIN IN TABLE1 TO CUST_CHAIN IN TABLE_CUST_CHAIN AS J2
JOIN CUST_TYPE  IN TABLE1 TO CUST_TYPE  IN TABLE_CUST_TYPE  AS J3

DEFINE FILE TABLE1
CUST_CHAIN/A5 = SUBSTR(10, LJUST(10, CUSTOMER, 'A10'), 1, 5, 5, 'A5');
CUST_TYPE/A5  = SUBSTR(10, LJUST(10, CUSTOMER, 'A10'), 1, 3, 3, 'A3');

CUST_INFO/A50 = 
IF CUST_DATA_TYPE EQ 'C' THEN TABLE_CUST_DESC.CUST_DESC       ELSE
IF CUST_DATA_TYPE EQ 'H' THEN TABLE_CUST_CHAIN.CUST_CHAIN_DESC ELSE
IF CUST_DATA_TYPE EQ 'T' THEN TABLE_CUST_TYPE.CUST_TYPE_DESC  ELSE '???';
END

TABLE FILE CUSTOMER
PRINT
CUST_INFO
...
END

Joe, as you will always be joining to those 3 look-up tables regardless of which one is actually needed, your query requires the use of outer joins to make sure your host table records are always retrieved even if no records exist in the parent tables.

The sample code Francis provided you with uses a one-to-one join structure which by definition implements a short-path record retrieval (equivalent to a SQL outer-join) which is exactly what you need as it is impossible for any given record to match all of the 3 look-up tables based on your rules.

Now, I know that this is the expected behaviour when using FOCUS data sources but I'm not sure if that automatic short-path (outer-join) is used when doing one-to-one joins in SQL-based database engines.

I don't have a DB2 environment to test it but just in case, if when running the sample code you obtain no records (which could be an indication of a regular inner join being used with no matching records in ALL of the tables), make sure you add:

SET ALL=ON

just before the TABLE FILE command and see if it makes a difference.

I hope this makes sense.

Ok, the solution seems simple enough, but i keep getting an error where it can't find the fields used in the join.

"THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE;"

Is there a restriction that i can't use a join on define fields?

Try this:

JOIN CUST_CUST  WITH CUSTOMER IN TABLE1 TO CUSTOMER   IN TABLE_CUST_DESC  AS J1
JOIN CUST_CHAIN WITH CUSTOMER IN TABLE1 TO CUST_CHAIN IN TABLE_CUST_CHAIN AS J2
JOIN CUST_TYPE  WITH CUSTOMER IN TABLE1 TO CUST_TYPE  IN TABLE_CUST_TYPE  AS J3

DEFINE FILE TABLE1
CUST_CUST/A10 = SUBSTR(10, LJUST(10, CUSTOMER, 'A10'), 1, 10, 10, 'A10');
CUST_CHAIN/A5 = SUBSTR(10, LJUST(10, CUSTOMER, 'A10'), 1, 5, 5, 'A5');
CUST_TYPE/A5  = SUBSTR(10, LJUST(10, CUSTOMER, 'A10'), 1, 3, 3, 'A3');

CUST_INFO/A50 = 
IF CUST_DATA_TYPE EQ 'C' THEN TABLE_CUST_DESC.CUST_DESC       ELSE
IF CUST_DATA_TYPE EQ 'H' THEN TABLE_CUST_CHAIN.CUST_CHAIN_DESC ELSE
IF CUST_DATA_TYPE EQ 'T' THEN TABLE_CUST_TYPE.CUST_TYPE_DESC  ELSE '???';
END

SET ALL=ON
TABLE FILE CUSTOMER
PRINT
CUST_INFO
...
END

It is assumed that "CUSTOMER" is the actual field in TABLE1.

The defined-based join will use CUST_CUST, CUST_CHAIN and CUST_TYPE which are derived from CUSTOMER.

The join structure requires an "anchor" field to attach the virtual fields to a physical table. That's what "JOIN ... WITH CUSTOMER ..." will attempt to do.

Have you considered implementing that logic directly in your database maybe through a view?

I am not sure that the SQL translator to your iWay DB2 adapter will be able to create a SQL expression that can be resolved by your database. If that's the case, you'll find that WebFOCUS will attempt to do the join itself instead of letting the database to the heavy lifting which may have a negative impact on performance.

I don't have a DB2 environment and don't remember its specific SQL functions but this Oracle-based code could give an illustration of what you could do:

create or replace view v_customer_information as
with x as (
   select customer_information.*,
          trim(substr(info, 1, instr(cust_info, '#') - 1))  cust_info_type,
          trim(substr(info, instr(cust_info, '#') + 1))     cust_info_value
    from customer_information
)
select ..., /* list of fields from CUSTOMER_INFORMATION table you want to expose */
       case cust_info_type when 'Customer' then cust_info_value else null  end customer_id,
       case cust_info_type when 'Customer Chain' then cust_info_value else null  end customer_chain,
       case cust_info_type when 'Customer Type' then cust_info_value else null  end customer_type
  from x

This assumes CUSTOMER_INFORMATION is your table and CUST_INFO is the field containing the joining information and that needs to be parsed.

You can then create a WebFOCUS synonym on V_CUSTOMER_INFORMATION an join to the look-up tables on CUSTOMER_ID, CUSTOMER_CHAIN ad CUSTOMER_TYPE respectively.

Not only does your WebFOCUS code look "cleaner" and easier to maintain but also the actual JOIN can be handled directly by the database reducing network traffic which can be considerable depending on the volume of information you'll be querying. If yours is a SUM request, you would also notice a significant performance gain as the database should be doing all of the data sorting and aggregation as well.

It's just an idea which may or may not be feasible depending on the flexibility you have when creating supporting reporting structures in your database (which seems to be a limitation in certain environments).

I resolved my define issue by creating a hold file then doing the joins. So the initial logic worked, i just had to add a hold file to be able to join on a non-defined field, not sure why.

Performance could use some help, i may play around with my views to relieve, but for now, i will call this solved, thanks a lot for the help everyone.