i’ve a problem with the lookup_ext function.
If i create a new lookup_ext function, i have the possibility to do it for Tables, Flat Files and Current Schemas.
Now i have the requirement to do a “myself”-Lookup on the Query defined in the “Schema In”.
So if i now selected “Current Schemas” and select the query in as lookup-table it’s not getting displayed as a lookup-table.
So at the moment i cannot use this function to do the “myself”-Lookup.
Background:
In the source query i have IDs.
For every ID i have mutlitple information.
Now i want to nest the information based on the ID.
That means,
ID is the head, and the Details should be in a subschema.
How can i solve this?
My idea was to do a lookup_ext function on subschema where ID = ID.
Although it might look like as doable it is not. Example: Your first row is inputted into the transform, how would the lookup find the matching partner records - there is none being read yet, just the first one.
So no matter what you do here, data needs to be collected first and then based on this entire dataset be joined.
The second problem with your request is, lookup does no nesting. Connect the query where you did the lookup_ext() to another query. What is shown as nested in the first query is actually not nested, it is on the same level as the lookup itself. In other words, lookup_ext() as new-function-call is presented the same way as a nested structure but it is not.
Problem three, the lookup is the wrong function for you. One of its strengths is, it returns out of all matching records just one, whereas with a join, you would create a Cartesian product. But in your instance you want all rows matching one ID.
You connect your output to two queries, one does a select distinct on the ID, the other has all the attributes of this ID. In the next query you “join” these two “tables”, the from clause of the query schema is the ID_Query, the from clause of the nested schema is the ID_Attributes_Query plus a where clause joining the IDs.
i’ve already implemented your written solution (two queries and afterwards joining the information in another query with nesting)
This is working fine.
I just want to know if nesting data into a deep structure is possible with only 1 query out of a database table?
Therefore i thought about a “myself” lookup on the query…but as you said it’s not possible.
Thanks again for your help!
If i have new requests on that, i will write again.
Just on a side note, the 4.1 release due within the next weeks (it is available on service market place for download but to internal people only at the moment) will have an XML_Map transform to make things a bit more easy.
(BOB member since 2012-04-18)