Passing If Then Else To Database

[Flopper]

Greetings,

I'm struggling to pass the following statement to the server, via Selection Criteria, to improve processing time... can anyone improve on this. I've tried some old techniques but i'm stumpred at present.

Cheers


(
If {?Morning/Evening}='Morning'
then CDate({INC_DATA.U_DATE4}) in DateTime(CurrentDate,Time(00,00,00)) to DateTime(CurrentDate,Time(23,59,59))
else
If {?Morning/Evening}='Evening'
then CDate({INC_DATA.U_DATE4}) in DateTime(CurrentDate,Time(00,00,00)) to DateTime((CurrentDate+1),Time(10,00,00))
)

 

[Turkbear]

Hi,
What does the 'Show Sql' show..Is there no Where clause?

You might try adding a default If test if neither is true:
( If..Then..else statements should be as specific and inclusive as possible so the parser will know what to do in any situation)

(If 
({?Morning/Evening}<>'Morning' and {?Morning/Evening}<>'Evening') then 
True
Else
If {?Morning/Evening}='Morning' 
then CDate({INC_DATA.U_DATE4}) in DateTime(CurrentDate,Time(00,00,00)) to DateTime(CurrentDate,Time(23,59,59))
else
If {?Morning/Evening}='Evening'
then CDate({INC_DATA.U_DATE4}) in DateTime(CurrentDate,Time(00,00,00)) to DateTime((CurrentDate+1),Time(10,00,00))
)

Hope it helps...



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Flopper]

Turkbear,

Apologies for not specifying but i'm using Crystal 8.5 so i know the limitations in passing statements to the SQl command. With regards to the Where clause no reference of the above formula is displayed, and unfortunately your code above does not seem to have any impact either.

Tanks for trying... and if you have any further ideas then please let me know!!!

 

[Turkbear]

Hi,
What database and version..What Connection type?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rosemaryl]

Dear Flopper,

Whenever a criteria like the above is being testy about passing to the where clause, I like to use the Switch statement ... it always (in my experience) passes.

Here is your formula rewritten. I did this because I do not know why you were modifying your datetime field to a date ... that isn't necessary. Also, when trying to pass to the where clause use Cdate and CDateTime rather then Date and Datetime ...



Using a Switch statement:

{INC_DATA.U_DATE4} in
Switch
({?Morning/Evening}='Morning',
CDateTime(CurrentDate,CTime(00,00,00)) to
CDateTime(CurrentDate,CTime(23,59,59)),
{?Morning/Evening}='Evening',
CDateTime(CurrentDate,CTime(00,00,00)) to
CDateTime((CurrentDate+1),CTime(10,00,00))
)

The above passes to the where clause no problem.

Regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Flopper]

Rosemary... you're a legend.

That method works a treat... yet another formula method to try and remember.

Thanks.