I have a field in a DynamicList that uses a ScalarQueryExpression. The problem that I am having is that the scalar expression is wrapped in a select * from (<scalar query expression>). This prevents me from using fields from the primary select list in the WHERE clause of the scalar query expression. I need to know what I'm doing that is causing this since the examples I've seen in the forum and elsewhere do not have this wrapping issue.

The code that I'm using to create the dynamic list is:

            // FinalGrade is the record with the highest SeqNo for the InternalId/Term/CompletedTermCourseSeqNo combination
            PredicateExpression subQueryFinalGrade = new PredicateExpression(
                (TermCourseGradeFields.InternalId == TermCourseGradeFields.InternalId.SetObjectAlias("TermCourseGrade2")) &
                (TermCourseGradeFields.Term == TermCourseGradeFields.Term.SetObjectAlias("TermCourseGrade2")) &
                (TermCourseGradeFields.CTCSeqNo == TermCourseGradeFields.CTCSeqNo.SetObjectAlias("TermCourseGrade2")));
            PredicateExpression maxSeqNoFinalGrade = new PredicateExpression(
                new FieldCompareSetPredicate(
                    TermCourseGradeFields.SeqNo, null,
                    TermCourseGradeFields.SeqNo.SetAggregateFunction(AggregateFunction.Max).SetObjectAlias("TermCourseGrade2"), null,
                    SetOperator.Equal,
                    subQueryFinalGrade));
            PredicateExpression queryFinalGrade = new PredicateExpression(
                (CompletedTermCourseFields.InternalId == RegisteredTermCourseFields.InternalId) &
                (CompletedTermCourseFields.Term == RegisteredTermCourseFields.Term) &
                (CompletedTermCourseFields.CRN == RegisteredTermCourseFields.CRN));
            RelationCollection relations = new RelationCollection();
            relations.Add(_RelationCompletedTermCourse_TermCourseGrade);
            RelationPredicateBucket finalGradeFilter = new RelationPredicateBucket(queryFinalGrade);
            finalGradeFilter.PredicateExpression.Add(maxSeqNoFinalGrade);
            ScalarQueryExpression selectFinalGrade = new ScalarQueryExpression(
                TermCourseGradeFields.FinalGrade,
                queryFinalGrade,
                relations);
            EntityField2 finalGrade = new EntityField2("FinalGrade", selectFinalGrade);

            ResultsetFields fields = DynamicListAdapter.BuildResultsetFields(new EntityField2[] {
                RegisteredTermCourseFields.InternalId,
                RegisteredTermCourseFields.Term,
                RegisteredTermCourseFields.CRN,
                RegisteredTermCourseFields.PartOfTerm,
                RegisteredTermCourseFields.RegistrationStatus,
                RegisteredTermCourseFields.CreditHours,
                RegisteredTermCourseFields.MidtermGrade,
                RegisteredTermCourseFields.AcademicLevel,
                finalGrade,
            });

            RelationPredicateBucket filter = new RelationPredicateBucket(PredicateExpression);

            // Set the sort order
            SortExpression sorter = null;

            switch (Sort)
            {
                case RegisteredTermCourseSortOrder.None:
                    break;
                case RegisteredTermCourseSortOrder.Term:
                    sorter.Add(RegisteredTermCourseFields.Term | SortOperator.Ascending);
                    break;
                case RegisteredTermCourseSortOrder.TermCRN:
                    sorter.Add(RegisteredTermCourseFields.Term | SortOperator.Ascending);
                    sorter.Add(RegisteredTermCourseFields.CRN | SortOperator.Ascending);
                    break;
                default:
                    break;
            }

            // Create data reader
            return Globals.BannerDBAdapter.FetchDataReader(
                fields,
                filter,
                CommandBehavior.CloseConnection,
                0,
                sorter,
                true);

The generated SQL statement is:

SELECT "SFRSTCR"."SFRSTCR_PIDM" AS "InternalId",
       "SFRSTCR"."SFRSTCR_TERM_CODE" AS "Term",
       "SFRSTCR"."SFRSTCR_CRN" AS "CRN",
       "SFRSTCR"."SFRSTCR_PTRM_CODE" AS "PartOfTerm",
       "SFRSTCR"."SFRSTCR_RSTS_CODE" AS "RegistrationStatus",
       "SFRSTCR"."SFRSTCR_CREDIT_HR" AS "CreditHours",
       "SFRSTCR"."SFRSTCR_GRDE_CODE_MID" AS "MidtermGrade",
       "SFRSTCR"."SFRSTCR_LEVL_CODE" AS "AcademicLevel",
       (SELECT *
          FROM (SELECT "SHRTCKG"."SHRTCKG_GRDE_CODE_FINAL" AS "FinalGrade"
                  FROM ("SHRTCKN" INNER JOIN "SHRTCKG" ON
                        "SHRTCKN"."SHRTCKN_PIDM" = "SHRTCKG"."SHRTCKG_PIDM" AND
                        "SHRTCKN"."SHRTCKN_TERM_CODE" =
                        "SHRTCKG"."SHRTCKG_TERM_CODE" AND
                        "SHRTCKN"."SHRTCKN_SEQ_NO" =
                        "SHRTCKG"."SHRTCKG_TCKN_SEQ_NO")
                 WHERE ((("SHRTCKN"."SHRTCKN_PIDM" = "SFRSTCR"."SFRSTCR_PIDM" AND
                       "SHRTCKN"."SHRTCKN_TERM_CODE" =
                       "SFRSTCR"."SFRSTCR_TERM_CODE") AND
                       "SHRTCKN"."SHRTCKN_CRN" = "SFRSTCR"."SFRSTCR_CRN") AND
                       ("SHRTCKG"."SHRTCKG_SEQ_NO" =
                       (SELECT MAX("TermCourseGrade2"."SHRTCKG_SEQ_NO") AS "SeqNo"
                            FROM "SHRTCKG" "TermCourseGrade2"
                           WHERE ((("SHRTCKG"."SHRTCKG_PIDM" =
                                 "TermCourseGrade2"."SHRTCKG_PIDM" AND
                                 "SHRTCKG"."SHRTCKG_TERM_CODE" =
                                 "TermCourseGrade2"."SHRTCKG_TERM_CODE") AND
                                 "SHRTCKG"."SHRTCKG_TCKN_SEQ_NO" =
                                 "TermCourseGrade2"."SHRTCKG_TCKN_SEQ_NO"))))))
         WHERE rownum <= 1) AS "FinalGrade"
  FROM "SFRSTCR"
 WHERE (("SFRSTCR"."SFRSTCR_PIDM" = :InternalId1))

The SELECT * FROM() wrapper is preventing me from accessing SFRSTCR fields in the scalar query expression for FinalGrade.

My current workaround is to remove the scalar query from this dynamic list and retrieve the final grades individually as I read through the DataReader returned by this dynamic list. The original dynamic list used relations on the primary table to retrieve the final grade, but that SQL statement took 9 seconds to execute (returning 40 rows). Using a nested query in the SELECT statement reduced the time to 0.3 seconds in PL/SQL Developer, so I was trying to implement that logic.

I hope that I haven't made this too complicated. Thanks in advance for your help.

Hi,

Have you looked at this sample ?

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(OrderFields.CustomerID, 0);
fields.DefineField(new EntityField2("NumberOfOrders", 
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count),
                (CustomerFields.CustomerId == OrderFields.CustomerId))), 2);
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, null);

Which results in this SQL :

SELECT CustomerID,
(
    SELECT COUNT(*) 
    FROM Orders
    WHERE CustomerID = Customers.CustomerID
) AS NumberOfOrders
FROM Customers

Or you can add a ScalarQueryExpression to existing predicates, like i.e. :

            bucket.PredicateExpression.Add(New FieldCompareExpressionPredicate(RentalConfigFreeDayMemberFields.DateActiveFrom, Nothing, ComparisonOperator.GreaterEqual, _
                                           New ScalarQueryExpression(RentalConfigFreeDayMemberFields.DateActiveFrom.SetObjectAlias("subquery"), subFilter, sorter2)))

Kind regards, Danny

Yes, that was one of the ones that I saw. But, I don't see what is different between that one and mine that creates the wrapper.

I modified my first post a little bit....but no, I also don't see any difference at first glance. Maybe setting the scalarexpression at a later time to the aliased field ? It now results in a * instead of a single value, what a SCALAR function is supposed to do.

Are you working with the latest bits from LLBLGen ?

Kind regards, Danny

I'm using Version 2.0.0.0 Final (May 23rd, 2007).

Is the SELECT * present because I am using AggregateFunction.None? Does this mean that using a subquery in this manner outside the intended paradigm?

I guess not....you're still fetching one field in a TOP 1 select...

I think ScalarQueryExpression was ment to be used with aggregates to return a scalar result, not a resultSet (even if it contains one row/field because of filtering and Top n usage).

I think you need to re-formulate the SQL Query, that you want to execute.

Thanks for your help. As it turns out, I have had to move the retrieval of the final grade to a separate query (for other reasons as well as the one here).

I found out that the performance problem was not with the query, per se. My original version with joins (not the nested query in the SELECT statement) is the "correct" version for this scenario. The long processing time was Oracle's "fault" and we have not been able to determine why the join has a long processing time, especially when we are joining on PK fields, nor why a nested query of the same request is faster than a join in this scenario.

Anyway, I'll be using two queries and I thank you for your responses and help.