0

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.

Jia-Luo
  • 3,023
  • 5
  • 16
  • 17
  • Is @CUSTOMERID here multi-valued; or how is the 'HOBBY' file structured to allow you to have multiple customers per hobby. Also, is this a question about U2's UniJPA driver? If so, email u2askus@rs.com as the Product Manager would love to talk to you about your usage of it. Thanks! – Dan McGrath Aug 06 '13 at 18:19

2 Answers2

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.

Dan McGrath
  • 41,220
  • 11
  • 99
  • 130
  • I really like the `@filename` style you used in your example. I might suggest a structure of `@filename.dictname` as useful for any TRANS type dictionary. I like `@CUSTOMER.ID` and `@CUSTOMER.NAME` for the examples you used above. It makes it immediately clear that its a TRANS type, what file it is accessing data from, and what the attribute from that file is. – Shawn McKnight Aug 07 '13 at 01:51
0

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.

Richard Sitze
  • 8,262
  • 3
  • 36
  • 48
rbl
  • 1
  • Hi rbl, My answer was prior to their clarifying edit. If you look at the question history, you will see the structure was slightly different then before. Note that the asker suggestions that @HOBBYID is multivalued, so TRANS will not work for that field. It is unclear what is meant by 'virtual attribute' in their specific case though, so clarification of that could change the answer again. – Dan McGrath Aug 07 '13 at 21:47
  • Hey guys, sorry for the unclear question. I'm using u2jpa driver. I want to know the query that when I "LIST CUSTOMER", it can return me the CUSTOMER information (@CUSTOMERID, NAME, @HOBBYID, HOBBY). – Jia-Luo Aug 07 '13 at 22:24
  • @Dan McGrath, no offense meant, I don't see the history. I use universe, not unidata, so perhaps the TRANS functions differently there, but on universe it works just fine on multi-valued fields. – rbl Aug 12 '13 at 19:31