Sean:
Which is the difference between it:
WHERE 1 =
CASE
WHEN (Param <> 0) THEN 1
WHEN (Some OtherTest) THEN 1
WHEN (Yet another Test) THEN 1
ELSE 0
END
and it:
Param <> 0 OR SomeOtherTest OR YetAnotherTest
?
Because I get the same result with both ways.
Greetings.
Walter.
On Wed, Feb 25, 2015 at 12:51 AM, Venus Software Operations
venussof...@gmail.com [firebird-support] <firebird-support@yahoogroups.com>
wrote:
>
>
>
> On 23/02/2015 01:27 pm, Svein Erling Tysvær
> svein.erling.tysv...@kreftregisteret.no [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
>
>
>
