SQL request creation with more WHERE arguments

gorousek

I tried to create SQL request with more WHERE arguments, but some of them can be *.

SELECT * FROM table1 a, table2 b
WHERE a.id = b.plan_id
and a.spz = :1
and a.cname = :2
ORDER BY a.spz

Everything other is set, it is working fine, when I set both arguments to exact value.
The thing is how to create the argument, when it can be "any" value.
I tried to set it to '''' + '*' + '''' but it returns zero rows. Tried to set it to '*', it returns error.

I tried to create whole WHERE string in a variable and pass it:

SELECT * FROM table1 a, table2 b
WHERE :1
ORDER BY a.spz

but it returns error too, not depending on the content of the :1.

Any suggestion?

Mick.pl

Try to create your query on-line, not at the design time.
For example when user clicked the OK Button in a dialog or in MasterData.OnBeforePrint event when you know what where frase should be for the DetailBand.
After creating - remember to Open the query.


Mick

gorousek

gorousek wrote: »

I tried to create SQL request with more WHERE arguments, but some of them can be *.

SELECT * FROM table1 a, table2 b
WHERE a.id = b.plan_id
and a.spz = :1
and a.cname = :2
ORDER BY a.spz

Everything other is set, it is working fine, when I set both arguments to exact value.
The thing is how to create the argument, when it can be "any" value.
I tried to set it to '''' + '*' + '''' but it returns zero rows. Tried to set it to '*', it returns error.

I tried to create whole WHERE string in a variable and pass it:

SELECT * FROM table1 a, table2 b
WHERE :1
ORDER BY a.spz

but it returns error too, not depending on the content of the :1.

Any suggestion?

Solution is quite easy (thanks to OldÅ™ich ? m?­d):

SELECT * FROM table1 a, table2 b
WHERE a.id = b.plan_id
and (a.spz = :1 OR 'All' = :1)
and (a.cname = :2 OR 'All' = :2)
ORDER BY a.spz

Or you can change 'All' to '*' or any other, that DialogPage returns for the 'pick them all' condition.

Mick.pl

Yes you are right - the solution is quite easy as the where clause is quite easy.
But ...
1. In general case OR is implemented as very slow operation in all databases
2. If you must create more complicated query like, for example
...
where a.id in (and_here_a_dynamic_list_of_values_coming_from_a_dialog)

or depends of what user chose in a dialog (1) or (2), eg. only one of followig

(variant 1)
...
where c.symbol like 'text_given_in_a_dialog'

(variant 2)
...
where c.symbol NOT like 'text_given_in_a_dialog'

then you must search for different solutions.

I've been using dynamic queries for several years and that why I offer them as very efective way to solve such problems in each case.

Fine that you've found your solution and that it satisfies you.

Mick