Problem using NULLIF in a CASE expression

Поиск
Список
Период
Сортировка
От Bruno BAGUETTE
Тема Problem using NULLIF in a CASE expression
Дата
Msg-id 432175CF.6010203@baguette.net
обсуждение исходный текст
Ответы Re: Problem using NULLIF in a CASE expression  (Csaba Nagy <nagy@ecircle-ag.com>)
Re: Problem using NULLIF in a CASE expression  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
Hello,

I have a problem using NULLIF inside a CASE expression.

I explain the goal of my query : I have a table where there is two
fields : one varchar field and one INT8 field.
When the varchar field is empty I have to display the INT8 field.
If the varchar field is not empty I have to display a NULL value (even
if the INT8 field contains something).

BUT : that table is used by several applications and theses applications
does not always store NULL in the varchar field. Some applications store
an empty string '', or stores <space> char(s).
SO, I have to do a btrim and to get a NULL value when I just have ''
after the btrim.

I wrote another (and quite shorter!) SQL query to resume the problem :

SELECT CASE NULLIF(btrim(' A string', ' '), '')
        WHEN NOT NULL
          THEN NULL
        ELSE 6
        END AS type_id;

ERROR:  operator does not exist: text = boolean
HINT:  No operator matches the given name and argument type(s). You may
need to add explicit type casts.

Why this query does not accept the NULLIF ?
What can I do to solve that problem ?

Thanks in advance !

Bruno BAGUETTE.

В списке pgsql-general по дате отправления:

Предыдущее
От: Csaba Nagy
Дата:
Сообщение: Re: Support for Limit in Update, Insert...
Следующее
От: Csaba Nagy
Дата:
Сообщение: Re: Problem using NULLIF in a CASE expression