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]
