Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Help forumulating Query using FieldCompareSetPredicate
 

Pages: 1
LLBLGen Pro Runtime Framework
Help forumulating Query using FieldCompareSetPredicate
Page:1/1 

  Print all messages in this thread  
Poster Message
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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.
  Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# Posted on: 03-Jul-2008 16:57:15.  
Hi,

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

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


Grtz,
Danny


  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# 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?
  Top
DvK
User



Location:
Delft (centre), Netherlands
Joined on:
22-Mar-2006 10:43:57
Posted:
292 posts
# 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.

  Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 04-Jul-2008 13:41:41.  
Got it, thanks. Regular Smiley
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.