On Wed, Feb 25, 2015 at 12:51 AM, Venus Software Operations venussoftop@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

On 23/02/2015 01:27 pm, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] wrote:

 

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