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]