Help forumulating Query using FieldCompareSetPredicate

Posts   
 
    
saggett
User
Posts: 50
Joined: 12-Nov-2007
# Posted on: 03-Jul-2008 16:41:22   

Using Adapter, Targeting .NET 3.5, using June 28th v2.6 release.

I have a SQL query:

SELECT sp.Id FROM SpecialPrice sp WHERE EXISTS (SELECT sp2.Id FROM SpecialPrice sp2 WHERE sp2.ProductId = sp.ProductId AND sp2.CustomerId = sp.CustomerId AND sp2.VolumePackNo = sp.VolumePackNo AND sp2.EffectiveDate >= '03 April 2008' AND sp2.Id <> sp.Id)

Or in other words:

Fetch all special prices where there is another special price with the same product, customer and volume that has an effective date of 03 April 2008 or earlier.

That I'm trying to write as a LLBLGen filter, without success. I believe the FieldCompareSetPredicate is the appropriate Predicate type to use but I'm not sure. Any help would be much appreciated.

DvK
User
Posts: 318
Joined: 22-Mar-2006
# Posted on: 03-Jul-2008 16:57:15   

Hi,

The FieldCompareSetPredicate is the right way to go ! Use a SetOperator.Exist.

// C#
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    CustomerFields.CustomerID, null, OrderFields.CustomerID, null,
    SetOperator.Exist, (OrderFields.EmployeeID == 2)));

Grtz, Danny

saggett
User
Posts: 50
Joined: 12-Nov-2007
# Posted on: 03-Jul-2008 17:00:07   

But where the SpecialPrice table is involved twice (once in the fields being fetched, and again in the FieldCompareSetPredicate when specifying the fetch criteria for the set) isn't some kind of alias usage necessary?

DvK
User
Posts: 318
Joined: 22-Mar-2006
# Posted on: 03-Jul-2008 23:36:43   

yep, use an SetObjectAlias property to set the alias to SP2 on the predicate fields you're using to build your where clause in the SP2 select where you refer to the SP fields.

saggett
User
Posts: 50
Joined: 12-Nov-2007
# Posted on: 04-Jul-2008 13:41:41   

Got it, thanks. simple_smile