I have used FieldCompareSetPredicate for some simple stuff, but I have something more challenging that I need some help wrapping my head around and figure someone here, with more LLBLGen experience than I, may be able to quickly identify the approach I need.

Here is a simplification of the schema I am using:

Account

AccountID Name

Score

ScoreID AccountID StartDate Score

The query is as follows (and if this is a poor way to do it, please let me know - I don't claim to be a SQL guru!):

SELECT *
FROM Account
WHERE AccountID IN
(
    SELECT AccountID
    FROM Score
    WHERE ScoreID IN
    (
        SELECT s.ScoreID
        FROM Score s
        JOIN (SELECT AccountID, MAX(StartDate) as StartDate FROM Score GROUP BY AccountID) MaxDates
        ON s.AccountID = MaxDates.AccountID AND s.StartDate = MaxDates.StartDate
    )
    AND Score > 1 
)

Again, I am looking to be able to duplicate this in code, getting back a collection of Account records. I am at my wit's end on doing something this complicated and any direction is appreciated.

You shouldn't need subqueries to achieve what you want--you should be able to do it with joins.

I believe this would be a rough equivalent using the Northwind database:

SELECT
    c.CustomerId, 
    o.OrderDate
FROM 
    Customers c
    INNER JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE
    o.OrderDate > '1/1/97'  
GROUP BY
    c.CustomerId,
    o.OrderDate
HAVING
    MAX(o.orderdate) = o.OrderDate

This should be pretty straightforward in LLBL, but post again if you have problems translating it.

HTH,

Phil

psandler wrote:

You shouldn't need subqueries to achieve what you want--you should be able to do it with joins.

I believe this would be a rough equivalent using the Northwind database:

SELECT
    c.CustomerId, 
    o.OrderDate
FROM 
    Customers c
    INNER JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE
    o.OrderDate > '1/1/97'  
GROUP BY
    c.CustomerId,
    o.OrderDate
HAVING
    MAX(o.orderdate) = o.OrderDate

This should be pretty straightforward in LLBL, but post again if you have problems translating it.

HTH,

Phil

I appreciate your time and effort on this, but that doesn't work for me. I reworked your query to work with my example, and it doesn't select what I am looking for - whereas my query does. Let me provide the sample data and the output (I actually have this running in SQL Server to make sure I could replicate this):

Account
--------------------------------------------------------
1   Foo
2   Bar
3   Baz
4   Blah


Score
--------------------------------------------------------
1   1   2001-01-01  2
2   1   2002-01-01  5
3   1   2002-04-13  1
4   2   2005-05-05  4
5   2   2006-11-11  2
6   2   2004-08-08  6
7   3   2005-04-04  3
8   3   2004-02-01  1
9   4   2006-09-01  1
10  4   2002-07-07  4

After I run my query, as you see it above, I get the following (as expected):

Results
--------------------------------------------------------
2   Bar
3   Baz

If you know of an easier way of getting this result, that would be great; I know that the query I have gets what I want after doing many tests, I just have trouble wrapping my head around the LLBLGen equivalent (not even sure where to start).

In words, I am basically wanting to get the latest Score for each Account, and then I want to filter on those results (i.e. the part about Score > 1).

Thanks

My query was wrong (I see now ), but I don't think the results you are getting are correct.

You want a list of all accounts that have at least one score of greater than 3? I assume when you said "Score > 1" in your second post it was a typo.

But looking at your data, you should get three results. Scores with id 2, 4 and 6 are all greater than three, and they are all part of different accounts.

I believe this would be correct:

SELECT
    a.account_id,
    a.account_name  
FROM 
    account a
    INNER JOIN score s ON a.account_id = s.account_id
GROUP BY
    a.account_id,
    a.account_name
HAVING
    MAX(s.score) > 3

Returns:

account_id  account_name
----------- --------------------------------------------------
1          Foo
2          Bar
4          Blah

(The filter would still work in the WHERE clause, but you're right in that it's not really correct.)

Hopefully I got it right this time.

Again, post if you have problems with the LLBL translation.

Phil

(EDIT: corrected typos)

psandler wrote:

You want a list of all accounts that have at one score of greater than 3?

No, I want a list of all accounts that have their most recent score greater than n (3 or 2 or 1 or whatever value).

psandler wrote:

I assume when you said "Score > 1" in your second post it was a typo.

Actually, the first post was a typo (well, not really - I was playing around and copied the wrong query into the textbox - I corrected the original post).

Either way, your query is still wrong.

So, the part about it needing to be the latest score is the hard part. I don't know if that makes sense or not, but I am hoping Frans or someone with similar knowledge can give me a pointer or two that will really turn the light on for me.

Thanks for your willingness to help.

Ach, I totally misread your post (both of them, actually).

I think this is right. Of course, now my confidence is shattered.

SELECT 
    a.account_id,
    a.account_name  
FROM 
    account a
    INNER JOIN score s ON a.account_id = s.account_id
WHERE
    s.start_Date = (SELECT MAX(s1.start_date) FROM score s1 WHERE a.account_id = s1.account_id GROUP BY account_id)
GROUP BY
    a.account_id,
    a.account_name,
    s.start_date
HAVING
    MAX(s.score) > 1    

However, I think you're right and that it can't be done without the subquery.

Phil

I wanted to add:

As far as I know, you can't do a subquery (FieldCompareSetPredicate) in the FROM clause with LLBL. I believe the query I posted last can be translated to LLBL (assuming it's right ).

Post if you get stuck and I'll see if I can help. I now feel personally responsible for making sure you're able to get this working.

Phil

psandler wrote:

I wanted to add:

As far as I know, you can't do a subquery (FieldCompareSetPredicate) in the FROM clause with LLBL. I believe the query I posted last can be translated to LLBL (assuming it's right ).

Post if you get stuck and I'll see if I can help. I now feel personally responsible for making sure you're able to get this working.

Phil

Well, I am leaving to head home now - but I am going to be working on this in the morning and will check into it then. Thanks again, hopefully your help will not be in vain!

THis thread is continued in the helpdesk forum.

Btw, you can write the query as:

SELECT  * 
FROM    Account
WHERE   AccountID
        IN
        (
            SELECT  AccountID
            FROM    Score
            WHERE   ScoreID =
                    (
                        SELECT TOP 1 ScoreID FROM Score s
                        WHERE s.AccountID = Score.AccountID
                        ORDER BY StartDate DESC
                    )
                    AND
                    Score > 1
        )

Which leads to code as (from my bare head, while peeking into the ref manual (which is in the installation folder) so it might contain a small syntax error here and there)

EntityCollection<AccountEntity> accounts = new EntityCollection<AccountEntity>();
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Add(new FieldCompareSetPredicate(
    AccountFields.AccountID, null,      // field to compare
    ScoreFields.AccountID, null,        // set field (the field in the inner select)
    SetOperator.In,                     // the IN operator
    new PredicateExpression(new FieldCompareSetPredicate(       // the filter which selects from score
        ScoreFields.ScoreID, null,
        ScoreFields.ScoreID.SetObjectAlias("s"), null,
        SetOperator.Equal, 
        (ScoreFields.AccountID.SetObjectAlias("s")==ScoreFields.AccountID),
        null,
        1,                              // the TOP 1
        new SortExpression(ScoreFields.StartDate | SortOperator.Descending)))
            .AddWithAnd(ScoreFields.Score > 1)));       // append the AND (score > 1) filter to the predicateex.
        
using(DataAccessAdapter adapter=new DataAccessAdapter())
{
    adapter.FetchEntityCollection(accounts, filter);
}
    

Btw, this thread is continued in the helpdesk because the topicstarter started a thread there and wanted to continue there.

Frans (aka Otis) -

I decided to post here rather than have Jim continue with this through your helpdesk, since we thought it was different and really isn't.

Otis wrote:

THis thread is continued in the helpdesk forum.

Btw, you can write the query as:

SELECT  * 
FROM    Account
WHERE   AccountID
        IN
        (
            SELECT  AccountID
            FROM    Score
            WHERE   ScoreID =
                    (
                        SELECT TOP 1 ScoreID FROM Score s
                        WHERE s.AccountID = Score.AccountID
                        ORDER BY StartDate DESC
                    )
                    AND
                    Score > 1
        )

Which leads to code as (from my bare head, while peeking into the ref manual (which is in the installation folder) so it might contain a small syntax error here and there)

EntityCollection<AccountEntity> accounts = new EntityCollection<AccountEntity>();
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Add(new FieldCompareSetPredicate(
    AccountFields.AccountID, null,      // field to compare
    ScoreFields.AccountID, null,        // set field (the field in the inner select)
    SetOperator.In,                     // the IN operator
    new PredicateExpression(new FieldCompareSetPredicate(       // the filter which selects from score
        ScoreFields.ScoreID, null,
        ScoreFields.ScoreID.SetObjectAlias("s"), null,
        SetOperator.Equal, 
        (ScoreFields.AccountID.SetObjectAlias("s")==ScoreFields.AccountID),
        null,
        1,                              // the TOP 1
        new SortExpression(ScoreFields.StartDate | SortOperator.Descending)))
            .AddWithAnd(ScoreFields.Score > 1)));       // append the AND (score > 1) filter to the predicateex.
        
using(DataAccessAdapter adapter=new DataAccessAdapter())
{
    adapter.FetchEntityCollection(accounts, filter);
}
    

The SQL query you have above does work and is much simpler.

I was able to get your from-your-head code working (I only needed to add an empty string in the FieldCompareSetPredicate .ctor just before the maxNumberOfItemsToReturn value (for the object alias)), but it would blow up unless I included the following line:

filter.Relations.Add(AccountEntity.Relations.ScoreEntityUsingAccountID);

However, this code produces the following SQL, which is not giving the same results:

SELECT DISTINCT Account.AccountID
FROM Account INNER JOIN Score  
ON Account.AccountID = Score.AccountID
WHERE Account.AccountID IN 
        (
            SELECT Score.AccountID 
            FROM Score 
            WHERE Score.ScoreID = 
                (
                    SELECT TOP 1 s.ScoreID 
                    FROM Score AS s
                    WHERE s.AccountID = Score.AccountID 
                    ORDER BY Score.StartDate DESC
                ) 
                AND Score.Score > 1
        )

Considering the complexity of this, I am at a loss at what to do differently. Is the SQL being produced by your code not as you expect it? Or is it that we need to write it differently? Help is appreciated, as this one is driving me bananas at this point.

Thanks

Frans,

Nevermind - I figured it out: setting the object alias to "s" on the sort clause (ScoreFields.StartDate | SortOperator.Descending) fixed things for me. Hopefully that is all that was needed, it appears to be working now.

Thanks for your help!