Subject | Re: [firebird-support] Multiple WHERE clauses |
---|---|
Author | Venus Software Operations |
Post date | 2015-02-25T04:51:15Z |
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
Thanks a mill Set, I tried out your full suggestion. It works the way I wanted it to, see below.
WITH cteTemp(iID, dFromDt, dToDt) AS
(SELECT CAST(72 as integer) AS iID,
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 ...
FROM cteTemp
CROSS JOIN tDevelopment
JOINs ...
WHERE (cteTemp.iID > 0 AND tDevelopment.iID = cteTemp.iID)
OR (cteTemp.iID = 0 AND CAST(tDevelopment.tDT AS DATE) BETWEEN cteTemp.dFromDt AND cteTemp.dToDt)
...
Kind regards
Bhavbhuti