How to use TableRequestInfo UserFilter to add an or where clause

Data Abstract
1

I m using RO 9.4 and want to add an extra where clause by using DA4’s TableRequestInfo.UserFilter but this clause gets added with an AND condition.

How can I pass a complex OR condition from RO client side to the RO server side and have it appended to the SQL where clause correctly?

2

Try to use DynamicWhere expression.

It can be simple expression like

      ld :=lTable.DynamicWhere;
      ld.Expression := ld.NewBinaryExpression('','Role',dboLike,'%manager%');

or complicated expression like

      ld :=lTable.DynamicWhere;
      ld.Expression :=
         ld.NewBinaryExpression(
            ld.NewBinaryExpression(
              ld.NewBinaryExpression('','Role',dboLike,'%manager%'),
              ld.NewBinaryExpression('','Name',dboLike,'J%'),
              dboAnd
            ),
          ld.NewBinaryExpression(
            ld.NewField('','id'),
            ld.NewList(
              [ld.NewConstant(20),
              ld.NewConstant(22),
              ld.NewConstant(24)]),
            dboIn),
          dboOR);
3

Can you show me the dynamic where expression for a subquery
SELECT * FROM TableA WHERE tableAkey IN SELECT (tableBkey FROM table B WHERE clauseB)

4

I dont think that can be achieved using dynamic where expressions. I will probably suggest to put it on a stored procedure and run it as a command or as a table. It will be faster (sql execution) and will save you headaches with DA/RO.

5

if you rewrite your SQL request from

SELECT * FROM TableA WHERE tableAkey IN SELECT (tableBkey FROM table B WHERE clauseB)

with

SELECT
	TableA.*
FROM
	TableA
	INNER JOIN TableB ON (TableB.tableBkey = TableA.tableAkey)
WHERE
	{WHERE}

you can easily implement it with DynamicWhere

6

The problem is this.

We have a GUI application which allows all kinds of filtered access to some DB data
In 90% of the cases we want SELECT FROM A (and some joines) which is fast
In 5% of the cases we want to add some slow filter condition C1
In 5% of the cases we want to add some other slow filter condition C2
We don’t mind slow lookup in 10% of the cases but we don’t want to loose fast lookup in 90% of the cases.
Furthermore we don’t want to combine C1 and C2
Our remains the same so our DA interface remains the same.
So the SQL subclause is exactly what I need.

How can we keep our fast query but also support some advanced queries and have the same DA interface on the client side?

7

Why you can’t have SQL like

SELECT ...
FROM ...
WHERE
  {WHERE} AND
  (
       (:P1 = 0) OR
      ((:P1 = 1) AND ( c1_condition)) OR 
      ((:P1 = 2) AND ( c2_condition))
 )

it should cover all your cases. you just need to setup P1 parameter on client-side.

8

How do we create c1_condition dymanically?
This is often FIELDNAME IN (select FIELDNAME FROM OTHERTABLE WHERE ([somedynamiccondition])

9

try to transform x in SELECT ... with INNER JOIN , like

SELECT
	TableA.*
FROM
	TableA
	INNER JOIN TableB ON (TableB.tableBkey = TableA.tableAkey)
WHERE
	{WHERE}

after this, you can pass [somedynamiccondition] as dynamic where

10

I would need to test if this works, the problem is that we have about 30 lines of code that create an SQL expression using the DA4’s DynamicWhere object. We would need to rewrite this with all the dangers of making a mistake (SQL string encoding, etc.)
Support for subqueries via DynamicWhere would definately be the cleanest solution IMO.