unidata command query data from two files

Question

I have two files in my unidata database as followed.

CUSTOMER

    -@CUSTOMERID
    -NAME
    -@HOBBYID (multi-valued, virtual attribute)

HOBBY

    -@HOBBYID
    -HOBBY

CUSTOMER and HOBBY are a one-to-many relationship. I want to query a list of customers who fit into a certain hobby. What will the unidata command be?

Look forward to a solution. Thanks.

Answer 1

Here is a simple query you can run at the ECL prompt

LIST HOBBY WITH HOBBY = "MyHobby" @CUSTOMERID @CUSTOMERID.NAME

Let me explain this.

As you are looking at a many to 1 relationship, you can do this using virtual dictionary items if you start from the 'many' table' (I know, counter-intuitive right?)

LIST HOBBY

Here we are just instructing the engine to do a simple display of data from the HOBBY file.

WITH WITH HOBBY = "MyHobby"

I am making the assumption you have already created a dictionary item called HOBBY - probably a D-type that points to the right attribute.

@CUSTOMERID

Another assumption, you have a D-type dictionary item that points to the attribute in HOBBY which has the @CUSTOMERID. I'm assuming @CUSTOMERID is a single value for the sake of this answer. This is just telling LIST to display this piece of information from for each record

@CUSTOMERID.NAME

This is an I-type dictionary you will need to create if you haven't already. Basically, in attribute 2 od the dictionary item, you would have something like TRANS("CUSTOMER",@CUSTOMERID,2,"X"). This is telling it to read the record with an ID as found in @CUSTOMERID for the current hobby record and return you attribute 2 (I'm assuming attribute 2 is where you store the customer name, change as desired. "X" tells the TRANS function to just return an empty string the the CUSTOMER record cannot be found.

Answer 2

Dan,

I think you've assumed more than is given. The provided info does not suggest that there are links in both directions, only that hobby codes are contained in the CUSTOMER file.

Given the info offered, this should work:

LIST CUSTOMER WITH @HOBBYID "MyHobby" NAME @HOBBYID

or, if you want to use the HOBBY rather than the @HOBBYID to compare/select against:

LIST CUSTOMER WITH EVAL "TRANS(HOBBY,@HOBBYID,HOBBY,'X')" = "MyHobby" NAME @HOBBYID

Of course it would be even better if you had created a translate dictionary item in the CUSTOMER dictionary that could be used instead of using the 'EVAL ...' clause.