I have a newbie question as to how to formulate a query. I am using SelfServicing template.

I have 2 tables, Employee and Rating, and they are related on Employee_Id.

I am trying to identify high performing employees who have a rating of let's say A1 or A2.

So I wrote a filter like so:

relationsToUse.Add(EmployeeEntity.Relations.RatingEntityUsingEmployeeId)

Dim values As String() = New String() {"A1", "A2" }

filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, values)) filter.Add(EmployeeFields.EmployeeId = 100)

However, the problem is that I should be looking at the latest rating for this employee, whereas the above filter is looking for any rating record that has A1 or A2 perf rating. Is this possible to express in LLBLGen?

The Rating table may have ratings for many years for a single employee, like 2005, 2006 etc. I want to pick up high potential employees by looking at the latest record in the rating table for that employee. For some employees, the latest rating record may exist for 2007, but for others, the latest record may be any other year.

The corresponding SQL would like: Select * from employee a, rating b where a.employee_id = b.employee_id and a.employee_id = 100 and (b.PerfRating in ('A1', 'A2') or b.hipo_flag=1) and b.rating_id = (select max(rating_id) from rating c where c.employee_id = b.employee_id)

What do you mean by "latest". Please formulate it in a normal SQL query !

I have edited my original message to clarify. Please see above message. Thanks

For using a subquery in the Where : use ScalarQueryExpression

Can anyone explain this in slightly more verbose manner?

The sub query in the where clause is a co-related subquery and I could not find any example as to how to do that....

I haven't compiled it so there might be some syntax errors, but this is the idea:

' Set an alias on the employeeEntity: 
relationsToUse.Add(EmployeeEntity.Relations.RatingEntityUsingEmployeeId, "A", "B", JoinHint.Inner)
Dim values As String() = New String() {"A1", "A2" }
filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, "B", values))
' now add the filter which performs the MAX sort. Use a field compare expression predicate for that.
' The fieldcompareexpression predicate is created by comparing a field with an expression. 
' for the expression we'll use a ScalarQueryExpression object, which is effectively the select MAX... 
' check the reference manual for details about these classes as well. 
filter.Add(RatingFields.RatingId.SetObjectAlias("B") = _
    New ScalarQueryExpression(RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
        (RatingFields.EmployeeId=RatingFields.EmployeeId.SetObjectAlias("B")))

It doesn't alias the Rating inside the subquery as 'c', as that's not necessary.

I tried the above code and I get this exception:

Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Here is the code that I used:

relationsToUse.Add(EmployeeEntity.Relations.RatingEntityUsingEmployeeId, "A", "B", JoinHint.Inner)

                Dim values As String() = New String() {"A1", "A2"}

                filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, values) Or RatingFields.HipoFlag = 1)

                        
    filter.Add(RatingFields.RatingId.SetObjectAlias("B") = _
                    New ScalarQueryExpression(RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                        (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B"))))

I further changed the code to read a little different and added missing aliases:

relationsToUse.Add(EmployeeEntity.Relations.RatingEntityUsingEmployeeId, "B", JoinHint.Inner)

                Dim values As String() = New String() {"A1", "A2", "B1"}

                filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, "B", values) Or RatingFields.HipoFlag.SetObjectAlias("B") = 1)

            
                filter.Add(RatingFields.RatingId.SetObjectAlias("B") = _
                    New ScalarQueryExpression(RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                        (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B"))))

With the above code, I got this exception: An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a ScalarQueryExpression to a Decimal.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I also noticed in the generated SQL that Rating id is being set to a parameter which is not what i want. I want the rating = the result of the subquery:

AND ( "LPA_B1"."PERF_RATING" IN ('A1', 'A2') OR "LPA_B1"."HIPO_FLAG" = 1) AND "LPA_B1"."RATING_ID" = :RatingId8

Can you suggest as to what I am doing wrong

Instead of:

 filter.Add(RatingFields.RatingId.SetObjectAlias("B") = _
                    New ScalarQueryExpression(RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                        (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B"))))

Try using the following more verbose code:

filter.Add(_
New FieldCompareExpressionPredicate(_
RatingFields.RatingId.SetObjectAlias("B"),_
ComparisonOperator.Equal,_ 
New ScalarQueryExpression(_
RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
(RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B")) ) ))

No this did not work either. The exception was: An exception was caught during the execution of a retrieval query: ORA-00904: "LPA_B1"."Employee_ID": invalid identifier . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I see that 2 separate SQLs were generated:

The first one was:

SELECT * FROM (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) WHERE rownum <= 1

The second one is too long to paste, but the relevant portions are:

.....
AND ( "LPA_B1"."PERF_RATING" IN (:PerfRating4, :PerfRating5) OR "LPA_B1"."HIPO_FLAG" = :HipoFlag7) 
AND "LPA_B1"."RATING_ID" = (SELECT * FROM (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" 
FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) WHERE rownum <= 1) 

In the second query above, which is the "main" query, when I ran it in in TOAD, It complained that "LPA_B1"."Employee_ID" is an invalid identifier. I believe that the offending code is:

(SELECT * FROM (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" 
FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) WHERE rownum <= 1) 

If I remove the Select * from portion (which is really not needed because a single value is being returned, the query executes fine. Is it possible to not generate the "Select * from" portion?

The code I am using is:

                relationsToUse.Add(EmployeeEntity.Relations.RatingEntityUsingEmployeeId, "B", JoinHint.Inner)

                Dim values As String() = New String() {"A1", "A2", "B1"}

                filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, "B", values) Or RatingFields.HipoFlag.SetObjectAlias("B") = 1)

    filter.Add( _
                New FieldCompareExpressionPredicate( _
                RatingFields.RatingId.SetObjectAlias("B"), _
                ComparisonOperator.Equal, _
                New ScalarQueryExpression( _
                RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B")))))

BTW, I am using SD.LLBLGen.Pro.DQE.OracleMS.NET20 as the runtime library

The exception you got from my code snippet suggests there is already a relation in the Relationscollection, which one? Could you please post the entire routine? You alias the two entities in the relation in my snippet so the relation you already ADDED also has to have the same entity aliased.

Example: // no entity is aliased. relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID); // error. Order isn't aliased in the relations already in the list (see line above) // however in this line it is aliased. relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID, "O", "OD", JoinHint.Inner);

this is an error as it otherwise will end up in sql like: FROM Customer INNER JOIN Order ON Customer.CustomerID = Order.CustomerID

and then what should be done? Order is referred to as 'O', however there's no Order with alias "O" in the FROM clause.

So the first line in my example in this post, should have been: relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID, "O");

Is this similar to your case?

No, I do not think the Rating table is added to the Relations twice. Let me try to clarify the problem once again. The problem is in the generated SQL. Here is what is generated:

SELECT * FROM (SELECT DISTINCT "CHDS"."EMPLOYEE"."EMPLOYEE_ID" AS "PersonnelId", "CHDS"."EMPLOYEE"."CAI" AS "Cai", "CHDS"."EMPLOYEE"."FIRST_NAME" AS "FirstName", "CHDS"."EMPLOYEE"."LAST_NAME" AS "LastName", "CHDS"."EMPLOYEE"."ADMIN" AS "Admin", "CHDS"."EMPLOYEE"."UPDT_TS" AS "UpdtTs", "CHDS"."EMPLOYEE"."UPDT_BY" AS "UpdtBy", "CHDS"."EMPLOYEE"."SBU_PDC_REP" AS "SbuPdcRep", "CHDS"."EMPLOYEE"."OPCO_PDC_REP" AS "OpcoPdcRep", "CHDS"."EMPLOYEE"."SUPERVISOR" AS "Supervisor", "CHDS"."EMPLOYEE"."OPCO_SPONSOR" AS "OpcoSponsor", "CHDS"."EMPLOYEE"."PWD" AS "Pwd", "CHDS"."EMPLOYEE"."ACTIVE" AS "Active", "CHDS"."EMPLOYEE"."ORGANIZATION_ID" AS "OrganizationId", "CHDS"."EMPLOYEE"."EMAIL_ADDRESS" AS "EmailAddress", "CHDS"."EMPLOYEE"."OFFICE_ASSISTANT" AS "OfficeAssistant", "CHDS"."EMPLOYEE"."OA_CAI" AS "OaCai", "CHDS"."EMPLOYEE"."READ_ADMIN" AS "ReadAdmin", "CHDS"."EMPLOYEE"."BUSINESS_UNIT" AS "BusinessUnit", "CHDS"."EMPLOYEE"."CONTACT" AS "Contact", "CHDS"."EMPLOYEE"."PWD_DATE" AS "PwdDate", "CHDS"."EMPLOYEE"."ALTERNATE_ADDRESS" AS "AlternateAddress", "CHDS"."EMPLOYEE"."WORK_SCHEDULE" AS "WorkSchedule", "CHDS"."EMPLOYEE"."AVAILABILITY_STATUS" AS "AvailabilityStatus", "CHDS"."EMPLOYEE"."AVAILABLE_DT" AS "AvailableDt", "CHDS"."EMPLOYEE"."ORGINATION_CAI" AS "OrginationCai", "CHDS"."EMPLOYEE"."ORGINATION_DT" AS "OrginationDt", "CHDS"."EMPLOYEE"."LOGIN_ID" AS "LoginId", "CHDS"."EMPLOYEE"."DATA_LAST_UPDT_DT" AS "DataLastUpdtDt", "CHDS"."EMPLOYEE"."DATA_LAST_UPDT_BY" AS "DataLastUpdtBy", "CHDS"."EMPLOYEE"."SHARE_ALL" AS "ShareAll", "CHDS"."EMPLOYEE"."SHARE_SKILL" AS "ShareSkill", "CHDS"."EMPLOYEE"."SHARE_EXPERIENCE" AS "ShareExperience", "CHDS"."EMPLOYEE"."SHARE_EDUCATION" AS "ShareEducation", "CHDS"."EMPLOYEE"."SHARE_LANGUAGES" AS "ShareLanguages", "CHDS"."EMPLOYEE"."SHARE_OTHER_TRAINING" AS "ShareOtherTraining", "CHDS"."EMPLOYEE"."SHARE_DOCUMENTS" AS "ShareDocuments", "CHDS"."EMPLOYEE"."IT_AUTHORIZATION" AS "ItAuthorization", "CHDS"."EMPLOYEE"."SHARE_URL" AS "ShareUrl", "CHDS"."EMPLOYEE"."SHARE_TEXACO_RESUME" AS "ShareTexacoResume", "CHDS"."EMPLOYEE"."EXTERNAL_CONTACTS" AS "ExternalContacts", "CHDS"."EMPLOYEE"."ALTERNATE_CONTACTS" AS "AlternateContacts", "CHDS"."EMPLOYEE"."PERSONAL_INTEREST" AS "PersonalInterest", "CHDS"."EMPLOYEE"."HOME_PAGE" AS "HomePage", "CHDS"."EMPLOYEE"."GAL_IND_CHANGE_DATE" AS "GalIndChangeDate", "CHDS"."EMPLOYEE"."SHARE_PERSONAL_INTEREST" AS "SharePersonalInterest", "CHDS"."EMPLOYEE"."SHARE_HOME_PAGE" AS "ShareHomePage", "CHDS"."EMPLOYEE"."USAGE_ACCEPT_DATE" AS "UsageAcceptDate", "CHDS"."EMPLOYEE"."SUCCESSION_PLANNING" AS "SuccessionPlanning", "CHDS"."EMPLOYEE"."SHARE_RAM" AS "ShareRam", "CHDS"."EMPLOYEE"."MIDDLE_NAME" AS "MiddleName", "CHDS"."EMPLOYEE"."PREFERRED_NAME" AS "PreferredName", "CHDS"."EMPLOYEE"."TRUSTED_SOURCE" AS "TrustedSource", "CHDS"."EMPLOYEE"."GENDER" AS "Gender", "CHDS"."EMPLOYEE"."BIRTHDATE" AS "Birthdate", "CHDS"."EMPLOYEE"."WORK_PHONE" AS "WorkPhone", "CHDS"."EMPLOYEE"."SHARE_OUTSIDE_ACHIEVEMENTS" AS "ShareOutsideAchievements", "CHDS"."EMPLOYEE"."SUFFIX" AS "Suffix", "CHDS"."EMPLOYEE"."EPH_UID" AS "EphUid", "CHDS"."EMPLOYEE"."LEGAL_NAME" AS "LegalNamePersisted", "CHDS"."EMPLOYEE"."HOME_COUNTRY_CODE" AS "HomeCountryCode", "CHDS"."EMPLOYEE"."PROFILE_VERIFICATION_DATE" AS "ProfileVerificationDate", "CHDS"."EMPLOYEE"."PDC_REP" AS "PdcRep", "CHDS"."EMPLOYEE"."PRIMARY_FUNC_DISCIPLINE_ID" AS "PrimaryFuncDisciplineId", "CHDS"."EMPLOYEE"."SECONDARY_FUNC_DISCIPLINE_ID" AS "SecondaryFuncDisciplineId", "CHDS"."EMPLOYEE"."PRIMARY_FUNC_SUBDISC_ID" AS "PrimaryFuncSubdiscId", "CHDS"."EMPLOYEE"."SECONDARY_FUNC_SUBDISC_ID" AS "SecondaryFuncSubdiscId" 
 FROM  "CHDS"."EMPLOYEE", "CHDS"."RATING" "LPA_B1", "CHDS"."EMPLOYEE_DATA", "CHDS"."SALARY_GRADE_D" 
 WHERE  "CHDS"."EMPLOYEE"."EMPLOYEE_ID"="LPA_B1"."EMPLOYEE_ID" AND "CHDS"."EMPLOYEE"."EMPLOYEE_ID"="CHDS"."EMPLOYEE_DATA"."EMPLOYEE_ID" AND "CHDS"."SALARY_GRADE_D"."SALARY_GRADE"="CHDS"."EMPLOYEE_DATA"."SALARY_GRADE" AND ( ( "CHDS"."EMPLOYEE"."ACTIVE" <> :Active1 AND ( "CHDS"."EMPLOYEE"."TRUSTED_SOURCE" <> :TrustedSource2 OR "CHDS"."EMPLOYEE"."TRUSTED_SOURCE" IS NULL) AND UPPER("CHDS"."EMPLOYEE"."FIRST_NAME") LIKE :FirstName3 
 AND ( "LPA_B1"."PERF_RATING" IN (:PerfRating4, :PerfRating5, :PerfRating6) OR "LPA_B1"."HIPO_FLAG" = :HipoFlag7) 
 AND "LPA_B1"."RATING_ID" = (SELECT * FROM (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) WHERE rownum <= 1) AND "CHDS"."SALARY_GRADE_D"."SORT_ORDER" >= :SortOrder8)) 
 ORDER BY "CHDS"."EMPLOYEE"."LAST_NAME" ASC,"CHDS"."EMPLOYEE"."FIRST_NAME" ASC) WHERE rownum <= 300

The SQL generated is fine except this piece which is not liked by Oracle, and throws this exception: "LPA_B1"."Employee_ID" is an invalid identifier.

AND "LPA_B1"."RATING_ID" = (SELECT * FROM (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) WHERE rownum <= 1) 

It almost looks like Oracle does not like that deep a reference to an outer table, from within a subquery.

If this piece were to be written like this, it works fine:

AND "LPA_B1"."RATING_ID" = (SELECT MAX("CHDS"."RATING"."RATING_ID") AS "RatingId" FROM "CHDS"."RATING" WHERE ( "CHDS"."RATING"."EMPLOYEE_ID" = "LPA_B1"."EMPLOYEE_ID")) 

The question I have is: 1. Is it possible to not generate the Select * From (...) code. 2. Is it possible for me to express this part of the filter another way (such as in SQL) so that I can proceed with my work. This filter is part of a 'Search' routine, and based on parameters selected by a user on a search form, I cumulatively build the filter, so it would be a lot of work for me rewrite the entire routine. I just want to find a way to write this particular leg of the filter in SQL, assuming it cannot be done in LLBLGen query language.

Please post the code which creates this query. You posted parts of it, but not the lines before the snippet.

Here is the entire function that created the above query.

Public Shared Function Lookup(ByVal s As EmployeeSearch, ByVal CurrentlyLoggedOnUser As EmployeeEntity) As EmployeeCollection

            Dim filter As PredicateExpression = New PredicateExpression
            Dim retVal As EmployeeCollection = New EmployeeCollection
            Dim relationsToUse As RelationCollection = New RelationCollection()
        
            'Filter out inactive records
            filter.Add(EmployeeFields.Active <> -1)

            'Filter out records where trusted source is NE
            filter.Add(EmployeeFields.TrustedSource <> "NE" Or EmployeeFields.TrustedSource = DBNull.Value)

            
            If IsNothing(s.FirstName) = False Then
                Dim flpFirstName As FieldLikePredicate = New FieldLikePredicate(EmployeeFields.FirstName, String.Format("{0}%", s.FirstName.ToUpper()))
                flpFirstName.CaseSensitiveCollation = True

                filter.Add(flpFirstName)
            End If
            

            If s.HighPotential = True Then
                relationsToUse.Add(ChdsPersonnelEntity.Relations.RatingEntityUsingEmployeeId, "B", JoinHint.Inner)

                Dim values As String() = New String() {"A1", "A2", "B1"}

                filter.Add(New FieldCompareRangePredicate(RatingFields.PerfRating, "B", values) Or RatingFields.HipoFlag.SetObjectAlias("B") = 1)

                Dim sqe As ScalarQueryExpression = New ScalarQueryExpression( _
                RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B")))            

                filter.Add( _
                New FieldCompareExpressionPredicate( _
                RatingFields.RatingId.SetObjectAlias("B"), _
                ComparisonOperator.Equal, _
                sqe))
            End If
            

            'Sort by last name, first name
            Dim se As ISortExpression = New SortExpression
            se.Add(New SortClause(EmployeeFields.LastName, SortOperator.Ascending))
            se.Add(New SortClause(EmployeeFields.FirstName, SortOperator.Ascending))

            'We should restrict the search results based on the max PSG allowed to the currently logged on user
            Dim max_sort_order As Integer = CurrentlyLoggedOnUser.Max_Org_BU_PSG_Sort_Order

            relationsToUse.Add(ChdsPersonnelEntity.Relations.EmployeeDataEntityUsingEmployeeId)
            relationsToUse.Add(EmployeeDataEntity.Relations.SalaryGradeDEntityUsingSalaryGrade)

            filter.Add(SalaryGradeDFields.SortOrder >= max_sort_order)

           'Do not apply the max no of records for org_bu_admin people
           'because their results may further get filtered down.
            retVal.GetMulti(filter, s.Max_No_Of_Records, se, relationsToUse)

        Return retVal

End Function

Instead of:

                Dim sqe As ScalarQueryExpression = New ScalarQueryExpression( _
                RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max), _
                (RatingFields.EmployeeId = RatingFields.EmployeeId.SetObjectAlias("B")))            

                filter.Add( _
                New FieldCompareExpressionPredicate( _
                RatingFields.RatingId.SetObjectAlias("B"), _
                ComparisonOperator.Equal, _
                sqe))
            End If

Try:

                filter.Add( _
                New FieldCompareSetPredicate(_
                RatingFields.RatingId.SetObjectAlias("B"), _
                RatingFields.RatingId.SetAggregateFunction(AggregateFunction.Max),_
                SetOperator.In,_
                (RatingFields.EmployeeId.SetObjectAlias("B") = RatingFields.EmployeeId) ))

This solution has worked. Thank you very much for all your help.

Thanks

Arvinder Chopra