> 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
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
