Active Query Builder support area

How to best handle mandatory conditions

Andrew Kennard

8 years ago
updated 7 years ago
Completed

I doing the ground work for a CustomExpression builder

Forgetting that for the moment using the Criteria and Or columns how would you handle what I would call mandatory conditions

Simple example

You have table with fields for Total Sales, Area, Category

You want to ask of the database return me all the Total computer and TV sales that are above £100 for the following areas B1 B2 G1 G2

so in SQL

WHERE (TotalSales>100 and (Category='Computers' or Category='TV')) AND (Area='B1' or Area='B2' or Area='G1' or Area='G2')

This is an over simple example and yes I could have used the IN operator but I wanted to emphasize the mix of and's and or's

I assume all of this would have to go in the criteria cell because the Or columns are only ever Or'd with the Criteria and each other ?

If that is the case then perhaps an option to turn off the Or columns when using the custom expression builder as they probably have limited use ?

Replies 16

Newest first
Newest first Oldest first

8 years ago

I've not tested extensively but this does seem to work ticking both the Output column and the field in the Table

It doesn't tick the table if it is an expression eg RTrim(table.field) which is fair enough as there may or may not be a field within the expression

Reply Link

Andrey Zavyalov, PM

This fix is included in the newly released version.

Thanks very much.

I will test it when it is released along with the others that were done today. Thanks

Sergey Kraikov

I have changed the behaviour of the "Select" cell for the new rows.

Now it is checked for the new rows even if the expression entered by using custom expression editor.

The fix will be included in the next build.

It should also tick the column in the table if appropriate as it does now if you choose from combo

Thanks in advance

Thanks I thought I was missing something

Yes, all of the mentioned operators are valid.

There is no way to tick output column programmatically. We will make it ticked automatically for new rows.

To start with I have implemented a simple dialog for the custom expression builder for the Expression/Field column

I load the dialog with the contents of the CompletionsList

When I return one of these entries as a string and set it using NewExpression the Output column checkbox is not ticked as it would have been had I not been using the CustomExpressionBuilder and chosen it from the drop down combo

This is fine but what method should I use to tick the output column for the current row ?

Thanks

Thanks for the feedback, it will help me shape my approach.

In terms of "valid boolean SQL expression" are all of these valid ?

https://msdn.microsoft.com/en-US/library/ms188074(v=sql.90).aspx Comparison operators

https://msdn.microsoft.com/en-US/library/ms189773(v=sql.90).aspx Logical operators

Are there any addtional ones I've missed ?

Hello,

Actually you don't have to enter criteria in excel-style. You can type any valid boolean SQL expression to any Criteria or Or column cell. Active Query Builder just performs additional processing of the entered text: if it founds a binary comparison operator without the left-side expression, it assumes that left-side expression is omitted and puts the expression from Expression column of this row in result SQL text.

Your right, working with the QueryTransformer API you won't see your changes in the text, but I thought that you're going to build a custom expression builder, i.e. an end-user interface to add mandatory conditions. So, I supposed that users should be able to see them in your cusom experssion builder UI.

Sorry, but we aren't going to add any custom columns to the Query Columns Grid.