Given this example of nested boolean logic, how would one construct the proper LLBLGen adapter code to perform this task?

SELECT SchoolName
FROM Schools
WHERE
SchoolType = 'Public'
AND (SchoolPublicType = 'Regular' OR SchoolPublicType = 'Vocational')
AND (SchoolLevel = 'Middle' OR SchoolLevel = 'High')

Any help or other suggestions would be greatly appreciated! My search criteria is much more complex than this, spanning nearly 20 fields, however I need a little push to get this going.

Thanks! Robert

As I'm working through this problem, I'm beginning to feel that the better solution to this problem is to use SQL subqueries. Since this is my first shot at a fairly (for me at least) complex search, I've got a little learning to do with regard to the subquery operations. I think I can solve this one on my own, but I'll post my solution for anyone who may run into this issue or has comments on how to improve upon it.

BTW, how can I see the SQL code that LLBLGen outputs using adapter? That would definitely be something that should be posted into the help file, as it would be very beneficial to see that!

Ok, so I've managed to work out this example into the following SQL code:

SELECT SchoolName
FROM Schools
WHERE
SchoolType = 'Public'
AND (SchoolID IN (SELECT SchoolID FROM Schools WHERE SchoolPublicType = 'Regular' OR SchoolPublicType = 'Vocational'))
AND (SchoolID IN (SELECT SchoolID FROM Schools WHERE SchoolLevel = 'Middle' OR SchoolLevel = 'High'))

Now on to converting this into LLBLGen code...

cycling321 wrote:

Given this example of nested boolean logic, how would one construct the proper LLBLGen adapter code to perform this task?

SELECT SchoolName
FROM Schools
WHERE
SchoolType = "Public"
AND (SchoolPublicType = "Regular" OR SchoolPublicType = "Vocational")
AND (SchoolLevel = "Middle" OR SchoolLevel = "High")

Any help or other suggestions would be greatly appreciated! My search criteria is much more complex than this, spanning nearly 20 fields, however I need a little push to get this going.

Thanks! Robert

Rule of thumb: every (...) block is a PredicateExpression. Also the complete WHERE clause is a predicate expression, so you create for each ( ) section a predicateexpression object and then add these to your final predicateexpression object. Similar to the Filtering and Sorting preface / introduction section in the documentation. (Using the generated code / adapter(selfservicing))

You can also use a CompareRange predicate, see teh HowDoI section for an example on that. (Best practises)

cycling321 wrote:

As I'm working through this problem, I'm beginning to feel that the better solution to this problem is to use SQL subqueries. Since this is my first shot at a fairly (for me at least) complex search, I've got a little learning to do with regard to the subquery operations. I think I can solve this one on my own, but I'll post my solution for anyone who may run into this issue or has comments on how to improve upon it.

As you know your values, a subquery isn't really necessary. A subquery performs a select inside a where clause (== predicate). You don't need a select as you already know hte values. A Field IN(a,b,c,d) clause is done via a CompareRange predicate.

BTW, how can I see the SQL code that LLBLGen outputs using adapter? That would definitely be something that should be posted into the help file, as it would be very beneficial to see that!

You can turn on tracing, which shows you the sql (if you switch on tracing for your database's Dynamic Query Engine (DQE)) inside vs.net's output window, if you run your program in debug mode. See for more details on how to do that the 'Troubleshooting and Debugging' section in the documentation

Thanks Frans!

I totally missed the part about being able to add PredicateExpressions within PredicateExpressions. You're right, a subquery does seem a bit overkill and unnecessary for this problem, I'm glad you stopped me before I pushed further! I'll remember the CompareRange predicate for use in the future, but you solved my problem with the nested SQL in having nested PredicateExpressions.

The tracing will help out a lot. Thanks for the pointer.

I guess I need to spend a couple of days reading every word of your beautiful documentation!

You've got a quality product here, and I'm very appreciative of your hard work. LLBLGen has signficantly boosted my productivity, and it's certainly worth more than what you're charging.

Best Wishes, Robert