asp.net 2.0 llblgenpro 2 self-servicing

Hello,

I am trying to convert this:

select unique tbl_student_visa_transfer.transfer_school, a.c, b.c
from tbl_student_visa_transfer
left join 
(select transfer_school, count(transfer_school) c from tbl_student_visa_transfer
where completion is null
group by transfer_school) a
on  a.transfer_school = tbl_student_visa_transfer.transfer_school
left join 
(select transfer_school, count(transfer_school) c from tbl_student_visa_transfer
where completion is not null
group by transfer_school) b
on b.transfer_school = tbl_student_visa_transfer.transfer_schoo

l

and here is my code:

 Dim fields As New ResultsetFields(3)
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 0, "items", "a")
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 1, "transferIn", "b", AggregateFunction.Count)
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 2, "transferOut", "c", AggregateFunction.Count)

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(fields(0))
        groupByClause.Add(fields(1))
        groupByClause.Add(fields(2))

        Dim filter As IPredicateExpression = New PredicateExpression()
        filter.Add(PredicateFactory.CompareValue(STUDENT_VISA_TRANSFERFieldIndex.COMPLETION, ComparisonOperator.Equal, DBNull.Value, "b"))
        filter.Add(PredicateFactory.CompareValue(STUDENT_VISA_TRANSFERFieldIndex.COMPLETION, ComparisonOperator.NotEqual, DBNull.Value, "c"))

        Dim relations As IRelationCollection = New RelationCollection()
        relations.Add(STUDENT_VISA_TRANSFEREntity.Relations.STUDENT_VISA_TRANSFEREntityUsingTRANSFER_ID, "a", "b", JoinHint.Left)
        relations.Add(STUDENT_VISA_TRANSFEREntity.Relations.STUDENT_VISA_TRANSFEREntityUsingTRANSFER_ID, "a", "c", JoinHint.Left)

        Dim dynamicList As New DataTable()
        Dim dao As New DaoClasses.TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, filter, relations, True, groupByClause, Nothing, 0, 0)

There is nothing return from the dynamic list. Does anyone know if I made any mistakes?

Thanks for your help!!!

Looks like you need to remove fields(1) and field(2) from the group by collection.

Jeff...

I removed them, but still nothing is returned.

Check your relationships. Looks like you're doing a self-join, but you're using the same relationship for transfer in and transfer out. Those should be different foreign keys. Can you post a CREATE TABLE for us to inspect your table definition?

Jeff...

create table TBL_STUDENT_VISA_TRANSFER
(
  TRANSFER_ID    NUMBER(10) not null,
  TRANSFER_SCHOOL VARCHAR2(100) not null,
  TYPE_OF_PROGRAM VARCHAR2(50) not null,
  COMPLETION      VARCHAR2(50),
  LENGTH_OF_STUDY NUMBER(2)
)
tablespace DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TBL_STUDENT_VISA_TRANSFER
  add constraint PK_TRANSFER_ID primary key (TRANSFER_ID)
  using index 
  tablespace DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Thanks

I think you should be using ScalarQueryExpressions in the selectlist. (refer to the manual's section: "Generated code -> Field expressions and aggregates")

Please try the following untested code:

        Dim fields As New ResultsetFields(3)
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 0, "items", "T")

        Dim filter1 PredicateExpression = ((STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL) And (STUDENT_VISA_TRANSFERFields.Completion = System.DBNull.Value))

        fields.DefineField(New EntityField("transferIn", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count), _
        filter1)), 1)

        Dim filter2 PredicateExpression = ((STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL) And (STUDENT_VISA_TRANSFERFields.Completion <> System.DBNull.Value))

        fields.DefineField(New EntityField("transferOut", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count), _
        filter2)), 2)

        Dim dynamicList As New DataTable()
        Dim dao As New DaoClasses.TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0)

I tried it, but

STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T")

gives me an oracle error: ORA-00904: "T"."TRANSFER_SCHOOL": invalid identifier.

Please enable DQE tracing and post the result query SQL the code you have produced plus the updated code so we can check if we made an error in the code we suggested. (DQE tracing: troubleshooting and debugging in the manual. )

My code:

 Dim fields As New ResultsetFields(3)
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 0, "items", "T")

        Dim filter1 As New PredicateExpression
        filter1.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
        filter1.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION = System.DBNull.Value)
        fields.DefineField(New EntityField("transferIn", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count), _
        filter1)), 1)


        Dim filter2 As New PredicateExpression
        filter2.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
        filter2.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION <> System.DBNull.Value)
        fields.DefineField(New EntityField("transferOut", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count), _
        filter2)), 2)
        Dim dynamicList As New DataTable()
        Dim dao As New DaoClasses.TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, Nothing, Nothing, True, Nothing, Nothing, 0, 0)

The result query:

SELECT "T"."TRANSFER_SCHOOL" AS "items", 
(SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL" 
FROM "TBL_STUDENT_VISA_TRANSFER" 
WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" 
AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NULL)) WHERE rownum <= 1) AS "transferIn", 
(SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
FROM "TBL_STUDENT_VISA_TRANSFER" 
WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" 
AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NOT NULL)) WHERE rownum <= 1) AS "transferOut" 
FROM "TBL_STUDENT_VISA_TRANSFER" "T"

I think the problem comes from the

SELECT * FROM (...) WHERE rownum <= 1)

It seems the inner query can't recognize the outer query. Is there any ways to remove that query?

wchan wrote:

The result query:

SELECT "T"."TRANSFER_SCHOOL" AS "items",
(SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
FROM "TBL_STUDENT_VISA_TRANSFER"
WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NULL)) WHERE rownum <= 1) AS "transferIn",
(SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
FROM "TBL_STUDENT_VISA_TRANSFER"
WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NOT NULL)) WHERE rownum <= 1) AS "transferOut"
FROM "TBL_STUDENT_VISA_TRANSFER" "T"

What is the DB error if you run such sql code in a DB query window?

I got "ORA-00904: "T"."TRANSFER_SCHOOL": invalid identifier." on the following line.

SELECT "T"."TRANSFER_SCHOOL" AS "items", (SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL" FROM "TBL_STUDENT_VISA_TRANSFER" WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NULL)) WHERE rownum <= 1) AS "transferIn", (SELECT * FROM (SELECT COUNT("TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL" FROM "TBL_STUDENT_VISA_TRANSFER" WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AND "TBL_STUDENT_VISA_TRANSFER"."COMPLETION" IS NOT NULL)) WHERE rownum <= 1) AS "transferOut" FROM "TBL_STUDENT_VISA_TRANSFER" "T"

Ok.

To solve this, I think you should do the following: - Do NOT alias the outer entity. Use the alias on the entity in the scalar queries.

Dim fields As New ResultsetFields(3)
fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 0, "items")
Dim filter1 As New PredicateExpression
filter1.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
filter1.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION = System.DBNull.Value)
fields.DefineField(New EntityField("transferIn", _
New ScalarQueryExpression( _
    STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("T"), _
filter1)), 1)

Dim filter2 As New PredicateExpression
filter2.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
filter2.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION <> System.DBNull.Value)
fields.DefineField(New EntityField("transferOut", _
New ScalarQueryExpression( _
    STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("T"), _
filter2)), 2)

This should use the 'T' alias on the tables INSIDE the scalar queries and not on the table in the main select. Oracle apparently evaluates from inside to outside and can't reapply an alias set to the outer select's table in a scalar in the select list.

I tried it.

 divTransfer.Visible = True
        Dim fields As New ResultsetFields(3)
        fields.DefineField(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL, 0, "items")

        Dim filter1 As New PredicateExpression
        filter1.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
        filter1.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION.SetObjectAlias("T") = System.DBNull.Value)
        fields.DefineField(New EntityField("transferIn", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("T"), _
        filter1)), 1)


        Dim filter2 As New PredicateExpression
        filter2.Add(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetObjectAlias("T") = STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL)
        filter2.AddWithAnd(STUDENT_VISA_TRANSFERFields.COMPLETION.SetObjectAlias("T") <> System.DBNull.Value)
        fields.DefineField(New EntityField("transferOut", _
        New ScalarQueryExpression(STUDENT_VISA_TRANSFERFields.TRANSFER_SCHOOL.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("T"), _
        filter2)), 2)

However, I got "ORA-00904: "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL": invalid identifier" on the following line. It's the same error I got before.

SELECT "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AS "items", (SELECT * FROM (SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL" FROM "TBL_STUDENT_VISA_TRANSFER" "T" WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AND "T"."COMPLETION" IS NULL)) WHERE rownum <= 1) AS "transferIn", (SELECT * FROM (SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL" FROM "TBL_STUDENT_VISA_TRANSFER" "T" WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AND "T"."COMPLETION" IS NOT NULL)) WHERE rownum <= 1) AS "transferOut" FROM "TBL_STUDENT_VISA_TRANSFER"

Hmm... I then wonder if it is possible to create a co-related scalar subquery inside a select list in Oracle, as I don't know how the SQL should be to make it work. The scalar queries are co-related with the main select, however you then should be able to refer to the elements in the FROM clause of the main select as you did in different ways already.

It might be the '1' specification which results in the select * from (select ... ) where rownum<=1) fragment, as it wraps the query with a rownum check as oracle doesn't have TOP support. ScalarQueryExpressions by default emit a 1 for max # of items to return.

if you rework the query as this:

SELECT "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AS "items",
(
    SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
    FROM "TBL_STUDENT_VISA_TRANSFER" "T"
    WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
    AND "T"."COMPLETION" IS NULL)
) AS "transferIn",
(
    SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
    FROM "TBL_STUDENT_VISA_TRANSFER" "T"
    WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
    AND "T"."COMPLETION" IS NOT NULL)
) AS "transferOut"
FROM "TBL_STUDENT_VISA_TRANSFER"

and execute it in toad or sql+, does it result a resultset or an error? (which oracle version are we talking about btw, 10g ?)

(edit) I see Oracle in general still doesn't support TOP, so the wrapper query is required. I think that's the issue as the correlated subquery has to be tied to its parent, but that's a wrapper query so THAT then has to be tied to its parent, but that's not done.

Hmm... difficult to solve... Could you confirm the query above works? If so, I'll see if I can add a fix to the llblgen pro code so you can execute the code on oracle so it won't emit the wrappers in scalarsubqueries in select lists.

SELECT "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL" AS "items",
(
    SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
    FROM "TBL_STUDENT_VISA_TRANSFER" "T"
    WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
    AND "T"."COMPLETION" IS NULL)
) AS "transferIn",
(
    SELECT COUNT("T"."TRANSFER_SCHOOL") AS "TRANSFER_SCHOOL"
    FROM "TBL_STUDENT_VISA_TRANSFER" "T"
    WHERE ( "T"."TRANSFER_SCHOOL" = "TBL_STUDENT_VISA_TRANSFER"."TRANSFER_SCHOOL"
    AND "T"."COMPLETION" IS NOT NULL)
) AS "transferOut"
FROM "TBL_STUDENT_VISA_TRANSFER"

This works fine. Yes...I'm using 10g.

You mean there is no way to remove "select * from (select ... ) where rownum<=1)" unless you change the code, right?

Yes, as it's the way TOP is implemented in Oracle and the ScalarQueryExpression class passes '1' as the limit for the fetch, which is OK as it has to deal with just 1 value max.

I'll look into this and see if I can add some code to fix this for you. I'll also check if you can add some custom code to make this work.

Otis wrote:

Yes, as it's the way TOP is implemented in Oracle and the ScalarQueryExpression class passes '1' as the limit for the fetch, which is OK as it has to deal with just 1 value max.

I'll look into this and see if I can add some code to fix this for you. I'll also check if you can add some custom code to make this work.

Just been playing with a scalar subquery in Sql Server 2000 (generated by LLBLGENPro) and found that removing the "TOP 1" from "TOP 1 COUNT(..." halved the execution time.

I mention it in this thread so that any changes you might make (with regard to emitting/not emitting TOP) might also be relevant to SqlServer too. If you want me to start a new thread, let me know.

Cheers Simon

I'm looking into removing it indeed. It's changing a 1 into a 0. THe thing is that the current scalarqueryexpression doesn't have a way to specify a top limit anyway, so the developer has to write a query which should return a single value.

Changing it in v2.0 will perhaps break applications, as it is a breaking change, so I'll change it in v2.5 and add a CTor which accepts a boolean flag to force a TOP clause. This is necessary because if you want to include the MAX value by doing an order by query instead of a MAX aggregate, it now won't work anymore.

There's another mistake in the code, ToQueryText isn't virtual . This reduces the way to easily solve this problem for v2.0 (which only plagues oracle and only in correlated scenarios).

I do agree that if TOP is hurting performance and also if it causes problems for execution, it should be removed. Though I can't add a breaking change to v2.0. I'll fix it in v2.5.

For the problem of this thread to fix it in v2.0, please do the following: - copy the ScalarQueryExpression class from the Runtime Library sourcecode and copy it to your own project - rename it to MyScalarQueryExpression class - change the '1' in the ToQueryText method's call to CreateSubQuery into a 0. - use instead of ScalarQueryExpression your MyScalarQueryExpression.

You're able to do this, as the code is BSD2 licensed to LLBLGen Pro licensees.

(edit). I see GetScalar() on adapter and in selfservicing also specify the '1' while executing ExecuteScalar under the hood. That's unnecessary as ExecuteScalar already returns a single value even if the resultset contains many rows.

Ok, in v2.5 this is now changed (in next build, later today): ScalarQueryExpression has 2 new CTors which accept forceRowLimit (default false). If set to true, it will emit a TOP 1 (or equivalent) otherwise it will leave it and assume the query results into a single value. It also has a property ForceRowLimit which you can use to set the flag.

It works fine now.

Thanks a lot!