nickausaus

ExecuteSQL with multiple AND conditions

Discussion created by nickausaus on Jul 2, 2016
Latest reply on Jul 4, 2016 by nickausaus

Hi all

I'm  quite new to the ExecuteSQL function in FM and wondering if someone can give me some guidance.

Basically what I'm trying to do is get a Fee  (number) from a table (Fees_Other) based on three conditions:

 

1. Fee_Name = 'Registration' (for this case, but I have many more to do...)

2. Effective From >= Purchase_Date

3. Effective To <= Purchase_Date

 

So basically I'm trying to get the applicable fee based on name, and effective dates. It works fine if I just have one Fee and only use the first condition, but I get the '?' result when I add the date clauses. Below is the Field Calculation I have at the moment:

 

ExecuteSQL ( "SELECT Fee FROM Fees_Other WHERE

Fee_Name = 'Registration'

and

Finance::Purchase_Date ≥ Fees_Other::Effective_From

and

Finance::Purchase_Date  ≤  Fees_Other::Effective To

"

; "" ; "")

Outcomes