You may consider WITH TMP(My72, cFrom, cTo, dValueFrom, dValueTo) as (SELECT CAST(:My72 as integer), US1.dValue, US2. dValue FROM RDB$DATABASE LEFT JOIN dSetValue('Current Year', 'From') US1 ON (1=1) LEFT JOIN dSetValue('Current Year', 'To') US2 ON (1=1)) SELECT <whatever> FROM TMP t CROSS JOIN tDevelopment d WHERE d.iID = t.My72 OR (t.MyParam is null AND CAST(d.tDT AS DATE) BETWEEN t.dValueFrom, t.dValueTo)
I’ve assumed the dSetValue procedure returns 0 or 1 record. I did this because the way you wrote things, the procedure would be executed twice for every row in tDevelopment, something not necessary unless result of the procedure depends on values in tDevelopment (which it doesn’t in your case, since ‘Current Year’, ‘From’ and ‘To’ are constants). HTH, Set From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 23. februar 2015 06:25 To: firebird-support@yahoogroups.com Subject: [firebird-support] Multiple WHERE clauses Hi all I have a SELECT statement which needs to have a WHERE clause adapt if a variable has a value or not: WHERE CASE WHEN 72 <> 0 THEN tDevelopment.iID = 72 ELSE (CAST(tDevelopment.tDT AS DATE) BETWEEN (SELECT US1.dValue FROM dSetValue('Current Year', 'From') US1) AND (SELECT US2.dValue FROM dSetValue('Current Year', 'To') US2)) END The value 72 is variable. What I need to do is when this value is a non zero I want to get only one record which matches the variable value and iID ELSE I want it to adapt to get a range of records as per stored dates Can we have such a WHERE clause? (The one above does not work for me) What if there are more such varied conditio ns to apply? Please advise Thanks and regards Bhavbhuti [Non-text portions of this message have been removed]