How to use a query to get distinct values from a item table

Question

Community,
I have created a staging table capability that will be used to generate innovator eBOMs from Oracle manufacturing BOMs.
My challenge is working with 1.7 million rows of data in SQL Server.
I know I could create a SQL procedure to get the unique (distinct) oracle Assembly IDs but I would like to use a Query Definition.
In the data model below, what is circled in RED is the Oracle Data. What is circled in Blue is where I am separating a&nbsp;&nbsp;&nbsp; 1) [multi-Division, multi-Ora Org, with non distinct Assemblies (by Ora Org)] : stg_Oracle_MfgItem_MfgItem
&nbsp; &nbsp;&nbsp; INTO
&nbsp;&nbsp;&nbsp; 2) [single-Division, multi-Ora Org, Distinct Primary Assemblies] stg_eBOM_PartBOM_Primary
&nbsp;&nbsp;&nbsp;&nbsp; AND
&nbsp;&nbsp;&nbsp; 3) [single-Division, multi-Ora Org, Other Assemblies (without Primary PartBOM)] stg_eBOM_PartBOM_Other
The goal is as follows: 
&nbsp; 2) stg_eBOM_PartBOM_Primary will be used to generate the innovator [Part BOM] relationships
&nbsp; 3) stg_eBOM_PartBOM_Other will be compared to stg_eBOM_PartBOM_Primary to derive Substitutes
So I would like to use a Query Definition to gather all the Distinct Oracle Assembly IDs so I can populate the stg_eBOM_PartBOM_Primary table.
Thanks in advance for your time and insight.
Scott

angelaip · Answer

Hi Scott
If Aras ever reintroduce their community awards, you already set the bar quite high for the "best description images for complex forum questions" category&nbsp;[emoticon:44a8a53ad3364ea78a16c5a3229f75bb]
Is your final goal a TreeGridView that shows the BOM based on the Oracle data? (So you don´t really create new items, but do something like a federated view?)
Or do you want to rebuilt the BOM / add new BOM items? For this use case, a Query Definition sounds like the hardest way to do the task.
Is this a task you do once for data migration, or shall the data be updated on a regular basis?

srmorrison · Answer

Hello Angela,
Thanks for the Shout out!
This is a data Migration/Transformation/Loading excercise. A tree Grid View will never be applied.
The Oracle mBOM Relational Data (structure) will be use to construct PLM eBOMS and PLM mBOMs, then it will be chucked.
It is being done for 1 division now and will be done for other divisions after the completion of this initial effort.
I wanted to use a Query Definition based:
1) Performance
2) Easily adaptable to other Divisions.
3) Trying to aviod the use of SQL.
4) Easier to pass off to the client for re-use.
The client has implemented mBOMs before eBOMs which will makes this very challenging assignment.
Here is an updated Data Model.

angelaip · Answer

Fortunately, the Aras award for “Customer who asked the Aras support team the strangest and most bizarre questions about trivial details in Innovator” will forever remain in my hands.[emoticon:0e469978eb8947d18b9c41123db3725a]
Unfortunately I am not the best person to answer questions regarding Query Definition. Maybe somebody else knows more about this one?

Question that I have right now:
- How can we "add" new items based on "get" Query Definition? Do you have done something like this before?- Do you made some attempts to build the Query Definition already? You mentioned that you want to build a query with DISTINCT. I wonder if this is possible with the regular Query Definition. You can build more complex Query Definitions if you don´t use the visual editor but edit the items that build the query directly (unhide the relationship in the Query Definition ItemType). But this will lead to the same problem you will have with SQL -&gt; it´s not obvious for other users anymore.
It´s not impossible to "pass" SQL components to other users. You can use the SQL ItemType and use a Method that does a sample call. In my case I have a whole bunch of Methods and SQL stuff with the name “Admin” or similar that is just intended to document certain special tasks. I even have a complete folder on the main server with "SQL scripts" for all kind of maintenance stuff, like repairs, cleanup, data validation, procedure tests, etc. .&nbsp;
But I understand the advantage of using the Query Definition, so&nbsp;I hope there is a solution!

srmorrison · Answer

Angela,
I am using the SQL Item to get the Unique Assembly item_number out of a 1.7 million row table.
And in fact .... do you remember the question I had about returning the data set to my Method?
Well, I figured what I was doing wrong. At the end of the SQL procedure I was not doing:
1) A "Select * From @AssyIds
Create Procedure stg_GetUniqueAssyIdsWithOrg(@creByDiv nVarchar(10))asBeginDeclare @AssyIds Table (rh_ora_mfg_org nVarChar(3), rh_ora_mfg_bom_assy_item_id nVarChar(20))
Insert @AssyIds Select Distinct omm.rh_Ora_Mfg_Org, omm.rh_Ora_Mfg_Bom_Assy_Item_Id  From [innovator].rh_Oracle_MfgItem_MfgItem as omm Where omm.rh_Plm_Cre_By_Div = 'AC' Order By omm.rh_Ora_Mfg_Org Desc
Select * From @AssyIdsEnd
Also&nbsp;
2) I Created a new Item to contain the result data. The item to get the procedure and run the procedure&nbsp; &nbsp; &nbsp;[getUniqueAssyIdsByOr]&nbsp; is different than the one to hold the data [uniqueAssyId]
&nbsp; &nbsp; &nbsp;When I used the same Item for both the data was not passed.
Item getUniqueAssyIdsByOrg = m_dal.NewItem("SQL", "SQL PROCESS"); getUniqueAssyIdsByOrg.setProperty("name", "stg_GetUniqueAssyIdsWithOrg"); getUniqueAssyIdsByOrg.setProperty("PROCESS", "CALL"); getUniqueAssyIdsByOrg.setProperty("ARG1", airCreatedByDiv); Item uniqueAssyIds = getUniqueAssyIdsByOrg.apply();
Now ... I don't know why this worked .... But it did.
I do hope that Aras adds this capability to the Query Definition.
Also, have you seen any documentation that explains how to use the relationships instead of the GUI.
I have done it when I replicated the BOM Structure (PE_MultiLevelReport) but all I did was replicate what Aras provided. I have no idea what I was actually doing.&nbsp;&nbsp;

angelaip · Answer

I remember your SQL question. I am not sure if it was you or some user community member, but what had confused me most were that people called SQL from client side. I didn´t know this was possible. From client side it seems not to be possible to get the return values, while it seems to work from server side.&nbsp;
Your code sample represents the classic procedure call from server side which should work.
You should be able to get property from your&nbsp;uniqueAssyIds&nbsp;to build an AML query that adds new items. One bottleneck is your amount of data. Adding 1 million datasets in one query will probably not work if you don´t own a datacenter with endless computing power.&nbsp;
What I often do for large data transfers from one itemtype to another is to create placeholder items and then add the actual properties by SQL.&nbsp;

Regarding modifying the Query Definition Relationships:
I assume it´s some kind of "illegal building technique" that Aras avoids to talk about&nbsp;to not confuse the average user even more.
I discovered it in one of the first versions of the QueryBuilder years ago, were a lot of UI features were still missing in the editor, but already there in the backend code. There are even old Github projects were I proudly shared my strange queries and I remember the Aras Labs teams was totally confused about what to do with them :).
Some general thoughts:
- There are QueryDefinition topics on the roadmap, especially GroupBy and Aggregate that will&nbsp;become very useful. We will get them sooner if people ask about them!
- We can call a QueryDefinition from a Method, which might be solution for "adding" new items based on the Query items. There is a blog article somewhere regarding this technique.

- Certain OOTB QueryDefinitions use custom logic that was not created by the visual editor, but by Methods. You even should be able to find them in your Innovator. For example Aras used Methods to convert old relationship views to QueryDefinitions when you Upgrade from 11 to 12.&nbsp;But I don´t know any official documentation about them,&nbsp;cause Aras probably never thought the average uses will&nbsp;need them.
Note that this discussion is right now already far beyond the average knowledge you get from official trainings. But that´s what the forum is for&nbsp;[emoticon:0e469978eb8947d18b9c41123db3725a]

Forum Discussion

How to use a query to get distinct values from a item table

6 Replies

Recent Discussions

Tech Docs - 'Zoom'

How to populate 'this' properties?

What causes the command bar buttons 'Save' and 'Done' to be greyed out?

Searching against a multi value list data type

Notify User When a Relationship is Added when the Source ItemType and Related ItemType have matching properties