Slow perf, Dynamic Record Selection 1

[fredp1]

CR 8.5, SQL Server 2000
I'm trying to get the record selection formula to be passed to the WHERE clause of the SQL. I'm only seeing the first 2 lines been passed. The remaining part, with the nested IF then else Block, is not been passed. (It gets processed on the client which slows the query down)

I've had a look around Tek-tips but still cannot get the rec selection to work efficiently.
Can anyone help?

//rec Selection
{MPDECH.EHECON} Like {?ECO Prompt} and
{MPDECH.EHECON} >= "0" and
(
if {?paramECO Status} = '1'then {MPDECH.EHECHS} < '80'
else if {?paramECO Status} = '2' then {MPDECH.EHECHS} = '80'
else if {?paramECO Status} = '3' then {MPDECH.EHECHS} = '85'
else if {?paramECO Status} = '4' then true
)
Thanks
Fred

 

[synapsevampire]

Try:

(
if {?paramECO Status} in ['1', '2'] then
{MPDECH.EHECHS} < '80'
else if {?paramECO Status} = '3' then
{MPDECH.EHECHS} = '85'
else if not({?paramECO Status} in ['1','2','3']) then
true
)
and
(
{MPDECH.EHECON} Like {?ECO Prompt}
)
and
(
{MPDECH.EHECON} >= "0" and
)

-k

 

[fredp1]

Hi Kai,

I tried that but only part of the record selection is passed to the WHERE clause. (Its the same as my original statement)

I've read our FAQ and the CD scr_recsel.pdf document but I cannot see what i'm doing differently.

Thanks
Fred

 

[tektipdjango]

Assuming your {MPDECH.EHECHS} field has a range between '00' and '99'

1- Create 2 formulas like this
@min :
if {?paramECO Status} = '1'then '00'
else if {?paramECO Status} = '2'then '80'
else if {?paramECO Status} = '3'then '85'
else if {?paramECO Status} = '4'then '00'

@max :
if {?paramECO Status} = '1'then '79'
else if {?paramECO Status} = '2'then '80'
else if {?paramECO Status} = '3'then '85'
else if {?paramECO Status} = '4'then '99'

then enter the following selection formula :

{MPDECH.EHECON} Like {?ECO Prompt} and
{MPDECH.EHECON} >= "0" and
(
{MPDECH.EHECHS} >= @min
)
and
(
{MPDECH.EHECHS} <= @max
)

 

[fredp1]

I can see how this will now work.
I'll try it and confirm that it works fine.

Looks like some of the other postings using a If statement in the record selection criteria was misleading, especially in V8.5. Sure, the if statement works, but the data is passed to the client for processing which can slow the query down.

This looks like a good tip.
Thanks a heap.

 

[tektipdjango]

I think that CR pass the SQL only if it follows the guidelines for optimization.
One guideline is to put the constants on the right side of the comparison and the field on the left

 

[synapsevampire]

Getting it to pass is part voodoo.

I have a FAQ here on passing SQL, but the important thing is to build it one piece at a time so that you know where the problem exists.

Using a formula as in django's post can sometimes help, though you might simplify it to just returning a single value from the formula.

You might also try:
(
if {?paramECO Status} = '1'then '00'
)
and
(
if {?paramECO Status} = '2'then '80'
)
and
etc...

-k

 

[fredp1]

Just to finish this tread off...

The solution from tektipdjango worked a treat.

I found by using IF THEN ELSE in the record selection it will process it locally.

I go unexpected results when I used your last suggestion Kai, it was evaluating both If statements as if they were true and passing it to the server. CR is doing something weird.
If I used
(
if {?paramECO Status} = '1'then {MPDECH.EHECHS} < '80' else true
)
if processes it locally.

Thanks again for all you help.
Fred