I am new to the U2 Universe so I've been researching about it and have been going through the documentation, searching on stackoverflow but still cannot figure out why a select all statement only returns one column - @ID. I've been attempting to do these select statements using u2netdk in SSIS.
SELECT * FROM ACCOUNT
I did some more research and found some posts vaguely describing columns have to be entered into an @. I wasn't sure what this means - is the @ a file or part of the file dictionary?
Also, I did manage to extract the dictionary of a file which lists all the columns available but when I tried to query some columns in SSIS using the ADO.NET connection manager, there would be an error that says column cannot be found. However, when I type the same statement in TCL, it would return rows with data. Why does this happen?
Thanks, JT
The real TL;DR here is to add a dictionary item of type "PH" called "@" and put the columns you want in there. If you want to use "SELECT *" you also need an "@SELECT" entry. Be mindful the multivalued and singlevalued fields may not play nicely here because they should be considered separate tables. Check out chapter 6 of the UniVerse document "SQL Administration for DBAs" which is titled "UniVerse Files and SQL" for insight into this.
Pick style data structures such as UniVerse are the original NOSQL as they existed in the before time when ANSI SQL was not yet a standard and the price for the space to store metadata was very, very high.
Executing SQL out of UniVerse is tricky because the data typing in SQL is defined explicitly, whereas the data and dictionary in Universe are separate structures and are stored and sometimes even used independent of one another. You MIGHT use the dictionary as a sort of key when you are reading or writing to figure out what type of data you are working with but there is nothing say you have to. Additionally the query language lets you change up what dictionary you are using whenever you want and data that does not fit the construct of the dictionary simply displays in a way that is unexpected instead of exploding fantastically. This can be incredibly freeing or lead onto madness, but that is another topic.
To make UniVerse play nice with SQL you have to define the Dictionary in such a way as to give it hooks into the the underlying structure of the data. When you read a record a record from Universe you are essentially reading out a full dataset with 1 or more tables nested up to 3 tiers deep. For your case, I would take all of the "D" and "I" type fields you want to access and put them in the "@" PH record of the dictionary. In the "@" it doesn't matter if they are multivalued or not. This will make the fields available to reference from SQL, though you could still have problems with data depending on your dictionary.
If you want to do "SELECT * FROM foo;" You need to have an "@SELECT" statement that is a subset of the "@" record containing only the singlevalued fields. You will want to make PH records of each multivalue association containing the associated fields to want and join then to get one unified query. Bear in mind that even with all of this SQL will respond differently at the TCL prompt than it will out through ODBC.
Here is a quick example. NAME and EMAIL are single valued and DATE and AMOUNT are multivalued.
@
PH
NAME EMAIL DATE AMOUNT
@SELECT
PH
NAME EMAIL
ORDERS
PH
DATE AMOUNT
Good Luck!