Hi --

I'm trying to write a filter that does the same thing that a SQL correlated subquery does. Basically, I have two tables with a 1:n (container, dip) relationship and I need to pull the container record as well as the "most recent" dip record.

I'm using a typed list to combine the two tables into one object.

The SQL query is: SELECT * FROM container c JOIN dip d ON c.container_id=d.container_id WHERE c.transfer_out_datetime IS NULL And c.wine_id = @Parameter AND Dip_Date = (SELECT TOP 1 MAX(DIP_DATE) FROM Dip d2 WHERE d.container_id = d2.container_id)

I've got a filter defined to represent the first two parts of the where clause, but I couldn't figure out how to add a correlated subquery.

Is it possible to use a correlated subquery with LLBL Gen Pro 1.0.2004.1? If so, could you point me to some documentation or provide pseudo code?

The database is SQL Server 2000.

Thanks, -Ryan Casey

Yes it is possible to specify a corelated subquery. You can specify aliasses for entities when you add a relation to the RelationCollection, and specify an object alias for a field.

As you use a typed list in selfservicing, you have to derive a class from it to set the relation's entity aliasses. This is solved soon in the upgraded designer, but till then, you have to do it manually.

The code below assumes the relation used is: ContainerEntity.Relations.DipEntityUsingContainerId

So derive a class from the typed list you've made. (add the proper constructors) Override BuildRelationSet() in your derived class. In the override, do: RelationCollection toReturn = base.BuildRelationSet(); toReturn[0].SetAliases("c", "d"); return toReturn;

Now, you can formulate a FieldCompareSetPredicate instance for the subquery. The field you compare should have "d" for ObjectAlias and the field you compare with (the set field) should have for ObjectAlias "d2".

Thanks for the response and code.

Otis wrote:

As you use a typed list in selfservicing, you have to derive a class from it to set the relation's entity aliasses. This is solved soon in the upgraded designer, but till then, you have to do it manually.

Is the upgraded designer you mention part of the "April/May" release?

Thanks, -Ryan

caseyry wrote:

Thanks for the response and code.

Otis wrote:

As you use a typed list in selfservicing, you have to derive a class from it to set the relation's entity aliasses. This is solved soon in the upgraded designer, but till then, you have to do it manually.

Is the upgraded designer you mention part of the "April/May" release? -Ryan

No, part of the "beta this week" release

Otis wrote:

No, part of the "beta this week" release

Excellent.