From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | tansley(at)law(dot)du(dot)edu |
Cc: | Postgresql Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Using CASE with a boolean value |
Date: | 2002-05-24 18:24:17 |
Message-ID: | 13268.1022264657@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Tom Ansley <tansley(at)law(dot)du(dot)edu> writes:
> CASE booking.quiz
> WHEN booking.quiz=false THEN 'No'
> WHEN booking.quiz=true THEN 'Yes'
> ELSE 'No'
> END
You seem to be confused about the two forms of CASE. You can either
write boolean WHEN conditions or provide a value to be compared against
a series of alternative match values. What you have here is an unholy
mixture of both, which would never have been accepted at all if
booking.quiz had not chanced to be a boolean value. The system will
take it as (booking.quiz = (booking.quiz=false)), etc. I'm far too lazy
to work out the exact implications of that, but it's probably not what
you want.
I'd write a CASE on a boolean value like this:
CASE WHEN booking.quiz THEN 'Yes' ELSE 'No' END
or if I wanted to distinguish UNKNOWN (NULL) as
CASE booking.quiz
WHEN true THEN 'Yes'
WHEN false THEN 'No'
ELSE 'Unknown'
END
Or you could write it as
CASE
WHEN booking.quiz=true THEN 'Yes'
WHEN booking.quiz=false THEN 'No'
ELSE 'Unknown'
END
which is actually what the system will expand the previous example into.
But writing it out seems un-idiomatic to me. (I always look at 'boolean
= TRUE' kinds of tests as the mark of a beginner programmer who hasn't
quite absorbed the notion of a boolean value...)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Ansley | 2002-05-24 18:54:45 | Re: Using CASE with a boolean value |
Previous Message | Manfred Koizar | 2002-05-24 17:22:29 | Re: query problem - get count in related table |