> I have a SQL query:
>
> SELECT
> CASE WHEN umowy.numer_umowy IS NULL THEN 0 END AS numer_umowy
> FROM umowy
> WHERE umowy.data_podpisania IS NOT NULL AND umowy.id_wnioski IN
> (SELECT CASE WHEN wnioski.id_wnioski IS NULL THEN 0 END AS id_wnioski
> FROM wnioski
> LEFT JOIN kategorie_wnioskow ON
> kategorie_wnioskow.id_kategorie_wnioskow=wnioski.id_kategorie_wnioskow
> WHERE kategorie_wnioskow.id_kategorie_wnioskow_typ=1) AND umowy.rok=2014
>
> How do I stop if "NUMER_UMOWY" is NULL is returned to "0" and not null
>
>After this query I have the result:
>
>"UMOWY.NUMER_UMOWY" = NULL
>
>It needs to
>
>"UMOWY.NUMER_UMOWY" = 0

Hi Łukasz!

Your statement is equivalent to "CASE WHEN umowy.numer_umowy IS NULL THEN 0 ELSE NULL END", so I think your problem simply is that NULL is returned if numer_umowy has a value. You may get the result you want by changing to:

CASE WHEN umowy.numer_umowy IS NULL THEN 0 ELSE umowy.numer_umowy END

A simpler way to write the same thing, is

COALESCE(umowy.numer_umowy, 0)

Notwithstanding that, I don't like the look of your query and think it is possible that Firebird may use more resources than necessary to arrive at the result. It is more likely that you want to use a statement like this:

SELECT coalesce(u.numer_umowy, 0) AS numer_umowy
FROM umowy u
WHERE u.data_podpisania IS NOT NULL
AND u.rok=2014
AND EXISTS(SELECT *
FROM wnioski w
JOIN kategorie_wnioskow kw
ON kw.id_kategorie_wnioskow=w.id_kategorie_wnioskow
WHERE coalesce(w.id_wnioski, 0) = coalesce(u.id_wnioski, 0)
AND kw.id_kategorie_wnioskow_typ=1)

If id_wnioski never has the value 0 (or you want NULL and 0 to be treated as two different values, you can replace

WHERE coalesce(w.id_wnioski, 0) = coalesce(u.id_wnioski, 0)

with

WHERE w.id_wnioski IS NOT DISTINCT FROM u.id_wnioski

HTH,
Set