Expression Clauses in a query

Question

Hello 
 
 We are trying to use a join clause in a script on 1IM. The problem we have is that when we are using classes .Join and .On of the object Query, .On class only admit ExpressionClause Type. Is there any example of how to declare and initialize this type? 
 
 Thanks in advance, 
 Adri

Markus Weiss-Ehlers · Accepted Answer

You should select only the columns needed in your where clause and not by a wildcard (.Select("*") or .SelectAll) as you did. The query object struggles in that case with duplicate (ambiguous) columns as you have run into. Please keep in mind that the Select is used for the Join when placed in the fluent interface at the position as in the sample code. 
 The correct code would be 
 Dim qApprover = Query.From(Table.PWOHelperPWO, "php").Join(Table.PersonWantsOrg, "pwo").On(Table.PWOHelperPWO.UID_PersonWantsOrg).
 Select(Table.PersonWantsOrg.DecisionLevel).
 Where("php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel")

adrian.perez3 · Answer

Hello Markus, 
 We tried this solution but we got an error in the exectuion of the query. 
 
 Our Query in 1IM: 
 Dim qApprover = Query.From("PWOHelperPWO","php").Join("PersonWantsOrg","pwo").On(Table.PWOHelperPWO.UID_PersonWantsOrg) _ .Select("*").Where("php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel") 
 
 The error we get: 
 Error during execution of statement: select php.Decision, php.IsFromDelegation, [...] XUserInserted, XUserUpdated from PersonWantsOrg) as pwo on php.UID_PersonWantsOrg = pwo.UID0 where (php.UID_PersonHead = 'e94fd1f7-ecf2-4d5f-9955-f2311a110c39' AND php.LevelNumber = pwo.DecisionLevel) order by UID_PersonWantsOrg, LevelNumber, UID_PWODecisionRule 
 Database error 209: Ambiguous column name 'UID_PersonWantsOrg'. Ambiguous column name 'UID_PersonWantsOrg'. 
 We think that the error may is produced because of the order by clause that we don't add or because of the .On clause. 
 
 As it was a bit difficult to solve, we nested the query in the where clause using UID_PWOHelperPWO IN (SELECT ....). 
 
 Thanks!

Markus Weiss-Ehlers · Answer

There is no sample as far as I know. But if you try to join over a foreign key column you do not need an ExpressionClause. 
 The following sample loads a collection of business roles where the org type (Org.UID_OrgType.Ident_OrgType) is "Business Roles". 
 Dim f As ISqlFormatter = Session.SqlFormatter()
Dim strOrgType As String = String.Empty

strOrgType = "Business roles"

Dim qOrgQuery As Query
qOrgQuery = Query.From(Table.Org).Join(Table.OrgType, "ot").On(Table.Org.UID_OrgType).Select(Table.OrgType.Ident_OrgType).Where( _
 f.Comparison(String.Format("ot.{0}", Table.OrgType.Ident_OrgType), strOrgType, ValType.String, CompareOperator.Equal))

Dim mycollection As TypedEntityCollection(Of VI.DB.Model.Org) = Session.Source.GetCollection(Of VI.DB.Model.Org)(qOrgQuery, EntityCollectionLoadType.Bulk)


Return mycollection.Count.ToString
 
 Please be aware that the join is just usable to identify the data to load. A collection always loads entities of one specific type. You cannot join additional columns to it.

Markus Weiss-Ehlers · Answer

No there isn't. A collection can only return values from the one object type. Option one is to create your own custom r-table that contains the joined values and create a collection from it. Option two is to code the additional data fetching and joining via additional collection loads and lookups in dictionaries or hash tables.

Expression Clauses in a query

Top Replies