Multivalues don't actually require an association. A multivalued attribute included in the @SELECT phrase will be presented as a separate table via Dynamic Normalization whether it has an association or not. There is no point having an association if the multivalued attribute stands alone.
An association is used to present multiple multivalued fields where there is a correspondence between the multivalues of separate attributes.
For example the details of an order might have the ProductCodes, Prices and Quantities as multivalues for each item in three separate attributes of the Order. The association will combine the corresponding multivalue for each attribute as columns in the normalized table with one row for each of the multivalues. The @SELECT need only refer to the association's PH DICT entry and the individual multivalues will be picked up as part of it.
Original Message:
Sent: 04-11-2024 17:34
From: deleuze jean
Subject: ODBC - Issue when import a S-ingle value / M-ultivalue
thank you for your reply Brian,
So I think I understand the idea.
When you deal with a single value field, it's easy to transform data for ODBC.
However, when you deal with a multi value field, you need to identify the association with the dictionary and ODBC could take information/data with a sub-table and not in the same table.
Ok for that, but pratically, how I can do that ? I know how identify the association but in excel for exemple when I open a table with ODBC, I construct one sql request to open data (and only single value are accessible). So, to open a subtable with multi value data, do I need to construct a "special request" with a specific "association keyword" ?
thank for your help
------------------------------
deleuze jean
Rocket Forum Shared Account
Original Message:
Sent: 04-10-2024 08:55
From: Brian Leach
Subject: ODBC - Issue when import a S-ingle value / M-ultivalue
Fundamentally, ODBC does not understand repeating or nested entities such as MultiValued fields, and clients like Excel would not know how to handle them. So UniVerse has to dynamically normalize these to create virtual sub-tables that they can understand.
If you have a standard UniVerse Orders table such as:
Order Id
Date
Time
Customer
Item]Item]Item
Price]Price]Price
Qty]Qty]Qty
It needs to separate these out into two tables to present to ODBC: Orders containing the Order Id, Date, Time and Customer; and Order Details containing Item, Price, Qty and Order Id - as you would find in a SQL database.
To follow up from what Manu said: each of these sub-tables is identified and named by an association of dictionaries.
For the example above, you would have fields that looked something like:
Item
D
4
MCU
Item
10L
M
Details
and a phrase in the dictionary that also binds them together, like:
Details
PH
Item Price Qty
You would also need to make sure these are exposed for ODBC. This would then appear as a separate Orders_Details (table) to ODBC (the fields will not appear in Orders).
ODBC is a slow protocol, with lots of overheads in terms of column descriptions etc. so it is best only to expose what you need. The fields may not have been exposed, or may need to be refreshed.
------------------------------
Brian Leach
Director
Brian Leach Consulting
Chipping Norton GB
Original Message:
Sent: 04-09-2024 05:18
From: deleuze jean
Subject: ODBC - Issue when import a S-ingle value / M-ultivalue
Hi Manu,
Thanks for your reply.
Sorry but I don't understand well,
Indeed, I see the "assocName" into field 7 of the dict.
But what does it mean "as a PH with fields list" ? If I modify assoc parameter with PH into the DICT I can read MV field with ODBC ?
When you say "uvodbc can't adress subvalue level" . Does it mean is impossible to read multivalue field with ODBC ?
Thanks for your help.
------------------------------
deleuze jean
Rocket Forum Shared Account
Original Message:
Sent: 04-08-2024 04:16
From: Manu Fernandes
Subject: ODBC - Issue when import a S-ingle value / M-ultivalue
Hi Jean,
Each MV field must be attached to a 'assocName' (field <7> of the dict).
Then you'll have a secondary table for each assoc into the mainfile, the table is named 'mainfile_assocName'
If you define, into the dict file, the assoc as a PH with fields list you limit the fields into the secondary table.
check the docs for details.
I hope this help.
ps : uvodbc can't adress subvalue level !
------------------------------
Manu Fernandes
Original Message:
Sent: 04-06-2024 06:08
From: deleuze jean
Subject: ODBC - Issue when import a S-ingle value / M-ultivalue
Hello all,
I use ODBC from rocket to open tables universe into excel.
It work pretty well for field with Single Value (S) but not at all for field with Multivalue (M)
Is it the normal behaviour ? I precise I can read the Multivalue (M) data into the universe shell.
Thank you for your help.
------------------------------
deleuze jean
Rocket Forum Shared Account
------------------------------