Can LLBLGen do a subquery in the select statement of SQL like the following SQL Statement?

Select 
    Institution_ID = a.Institution_ID,
    Name = a.Name,
    'Number Due' = (
            Select Count(*) 
            From RequiredAgencyWebForm r 
            Where r.Institution_ID = a.Institution_ID )
From 
    Agency a
Order by 
    a.Name

If so, how?

Thanks, John H.

John wrote:

Can LLBLGen do a subquery in the select statement of SQL like the following SQL Statement?

Select 
    Institution_ID = a.Institution_ID,
    Name = a.Name,
    'Number Due' = (
            Select Count(*) 
            From RequiredAgencyWebForm r 
            Where r.Institution_ID = a.Institution_ID )
From 
    Agency a
Order by 
    a.Name

If so, how?

Thanks, John H.

Yes, you can. You'll want to look into the "dyanamic lists" functionality included in the framework.

Dynamic Lists allow you to construct in code what amounts to a custom SQL SELECT statement. The great thing about them is that you're able to add in SQL expressions like what you're looking for above as in "Number Due" = ().

You can find more info in the documentation at:

Using the generated code::<scenario>::Using the typed list and typed view classes::Creating dynamic lists

And

Using the generated code::Field expressions and aggregates

I've to correct you on this, Jeff. The construction he uses is a select for a field using a subquery, inside the select list, which isn't supported yet. What he wants is not doable at the moment using another construct.

Otis wrote:

I've to correct you on this, Jeff. The construction he uses is a select for a field using a subquery, inside the select list, which isn't supported yet. What he wants is not doable at the moment using another construct.

Ooops My fault, John; sorry.

Jeff...

Is this something that is planned for a future release?

JH

John wrote:

Is this something that is planned for a future release?

JH

Yes, may/june update will have support for this.

Otis wrote:

John wrote:

Is this something that is planned for a future release?

JH

Yes, may/june update will have support for this.

Did this make it into the latest release on the site? (July 13, 1.0.2004.2)

I need to do something similar with a subquery in the select clause.

Thanks, -Ryan Casey

No, this is postponed till a later date.

When this is implemented, will it support CASE statements in the select clause? Also, any general timeframe when it will be implemented at all?

Case is undecided, as it is very db specific and often can be done on the client as well.

subquery selected values are planned, the next release they will probably be in is v2.0.