open

Latika6

New Member
open query query1 for <tablename> no-lock where <table.field1> = x and ( if var1 = 0 then field2 = y1 else field3 = y2)

In order to build a where clause there is an if..then. with the where clause. Will this cause the query to run slow?
 

Stefan

Well-Known Member
Since you are asking the question, probably.
One solution is to use a dynamic query in which the query is simplified before the query is opened.
 

Latika6

New Member
Since you are asking the question, probably.
One solution is to use a dynamic query in which the query is simplified before the query is opened.
if we are not using dynamic query, will a static query with if condition cause the query to run slow?
I can probably split it into two static queries?
 

TomBascom

Curmudgeon
Using an IF inside a WHERE is not 100% certain to result in a slow query. But it is pretty darn likely that a query that is written like that has some "issues". When I see that sort of code I expect to find room for improvement.

An embedded IF is almost certainly more difficult to read and understand. Which is reason enough not to do it.

Two distinct OPEN QUERY statements would be much more maintainable.

Dynamic queries really aren't that hard. Building the WHERE clause that way is a lot more flexible and also very maintainable. If you have not previously used dynamic queries there is some syntax to get used to but once you try a few the idea will grow on you.
 

Osborne

Active Member
If you really do not want to use dynamic queries you can use some of their methods on static queries:

Code:
DEFINE vWhere AS CHARACTER NO-UNDO.

DEFINE QUERY qCustomer FOR Customer SCROLLING.

vWhere = "FOR EACH Customer WHERE ...".

QUERY qCustomer:QUERY-PREPARE(vWhere).
QUERY qCustomer:QUERY-OPEN(). 
REPEAT:
  GET NEXT qCustomer.
  IF NOT AVAILABLE Customer THEN LEAVE.
  DISPLAY Customer.NAME.
END.
QUERY qCustomer:QUERY-CLOSE().
 

TomBascom

Curmudgeon
That's quite true! The hybrid approach lets you leverage the simplicity of old style static code like "DISPLAY customer.Name" with the flexibility of handle based stuff.
 
Top