Using the IF function in a WHERE clause

[duncanstreet2004]

Hi,

I need to build a query with a WHERE clause for a Crystal report that essentially tests for something and if true, builds the rest of the WHERE clause one way, and if false, builds the rest another way. I would like to be able to do something like this:

WHERE LeaseCompany.LeaseCompanyNum = '09' AND LeaseDatabase.LeaseTerminationDate = 0 AND
OpeninvAutoSchedule.AutoSchSeqNum = 1 AND LeaseEquipment.EquipSaleInvDate = 0 AND
IF(LeaseDatabase.LeaseMaturityDate > 20041104, OpeninvAutoSchedule.AutoschNum = 1,
OpeninvAutoSchedule.AutoScheduleStartDate1 >= LeaseDatabase.LeaseMaturityDate AND OpeninvAutoSchedule.AutoschNum > 1)

However when I try to run this I get an ODBC Error (right after the first OpeninvAutoSchedule.AutoschNum it chokes). Is it possible to do what I'm trying to do another way?

Thanks very much for any help or suggestions.


Robin

 

[mirtheil]

The IF function has the following format:
IF(<expression>,<True Value>, <False Value>)
Your IF doesn't seem to follow that structure.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[duncanstreet2004]

I thought it did -- it has each of the three parts, separated by commas, enclosed by parentheses. Can you tell me how my syntax is incorrect? Thank you!

 

[mirtheil]

Well, looking at it, it does seem to have the three parts separated by commas:
IF(LeaseDatabase.LeaseMaturityDate > 20041104, OpeninvAutoSchedule.AutoschNum = 1,
OpeninvAutoSchedule.AutoScheduleStartDate1 >= LeaseDatabase.LeaseMaturityDate AND OpeninvAutoSchedule.AutoschNum > 1)
But, "OpeninvAutoSchedule.AutoScheduleStartDate1 >= LeaseDatabase.LeaseMaturityDate AND OpeninvAutoSchedule.AutoschNum > 1" is an expression, not a value. The same is true of "OpeninvAutoSchedule.AutoschNum = 1". They are both expressions. What happens if you change it, for the sake of testing, to:
IF(LeaseDatabase.LeaseMaturityDate > 20041104,1,2)



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com