Thread: Using CASE with a boolean value

Hi all,

I'm trying to use a case statement with a boolean value.  i.e.  if the value =
false then output 'NO', if value = true then output 'YES'.  This is what I
came up with

CASE booking.quiz
    WHEN booking.quiz=false THEN 'No'
    WHEN booking.quiz=true THEN 'Yes'
    ELSE 'No'
END

But, it isn't working.  Everything compiles and the rows returned are correct
but it still returns 'true' or 'false' rather than 'YES' or 'NO'.  I've also
tried this

CASE booking.quiz
    WHEN booking.quiz='f' THEN 'No'
    WHEN booking.quiz='t' THEN 'Yes'
    ELSE 'No'
END

and this

CASE booking.quiz
    WHEN booking.quiz='false' THEN 'No'
    WHEN booking.quiz='true' THEN 'Yes'
    ELSE 'No'
END

Anybody got any ideas?  Should the value be cast into a string?

Thanks

Tom Ansley

> I'm trying to use a case statement with a boolean value.  i.e.
> if the value =
> false then output 'NO', if value = true then output 'YES'.  This
> is what I
> came up with
>
> CASE booking.quiz
>     WHEN booking.quiz=false THEN 'No'
>     WHEN booking.quiz=true THEN 'Yes'
>     ELSE 'No'
> END
>
> But, it isn't working.  Everything compiles and the rows returned
> are correct
> but it still returns 'true' or 'false' rather than 'YES' or 'NO'.
>  I've also
> tried this
>
> CASE booking.quiz
>     WHEN booking.quiz='f' THEN 'No'
>     WHEN booking.quiz='t' THEN 'Yes'
>     ELSE 'No'
> END
>
> and this
>
> CASE booking.quiz
>     WHEN booking.quiz='false' THEN 'No'
>     WHEN booking.quiz='true' THEN 'Yes'
>     ELSE 'No'
> END
>
> Anybody got any ideas?  Should the value be cast into a string?

create table bools (b bool);
insert into bools values (true);
insert into bools values (false);
select case b when true then 'yes' when false then 'no' else 'unknown' end
from bools;

 case
------
 yes
 no
(2 rows)

Seems to work just fine. Can you post a full example of this not working,
please?

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

Hi,

I'm not quite sure what you mean by full working example but here is the full
select statement and all the tables that it uses.  Also, I tried using the
idea mentioned and it still doesn't seem to work.

Cheers

Tom

SELECT booking.quiz, CASE booking.quiz WHEN booking.quiz='false' THEN 'No'
WHEN booking.quiz='true' THEN 'Yes' ELSE 'No' END, company.company_name,
person.last_name, person.first_name, booking.fk_seminar_code,
seminar_template.seminar_tmpl_name, seminar.seminar_start_date, hotel.city
FROM (seminar_template INNER JOIN (hotel INNER JOIN seminar ON hotel.id_hotel
= seminar.fk_id_hotel) ON (seminar_template.seminar_tmpl_type =
seminar.fk_seminar_tmpl_type) AND (seminar_template.id_seminar_tmpl =
seminar.fk_id_seminar_tmpl)) INNER JOIN ((company INNER JOIN person    ON
company.id_company = person.fk_id_company) INNER JOIN booking ON
person.id_person = booking.fk_id_person) ON (seminar.seminar_year =
booking.fk_seminar_year) AND (seminar.seminar_code = booking.fk_seminar_code)
AND (seminar.fk_seminar_tmpl_type = booking.fk_seminar_tmpl_type) AND
(seminar.fk_id_seminar_tmpl = booking.fk_id_seminar_tmpl) ORDER BY
person.last_name, person.first_name, company.company_name;

create table hotel (
    -- primary key
    id_hotel serial not null primary key,

    -- attributes
    hotel_name varchar(100) not null,
    address varchar(35),
    city varchar(21),
    state char(2),
    zip varchar(10),
    main_phone varchar(13),
    fax varchar(13),
    email varchar(128),
    caterer_name varchar(40),
    caterer_phone varchar(13),
    caterer_fax varchar(13),
    update_counter integer
);

create table company (
    -- primary key
    id_company serial not null primary key,

    -- attributes
    company_name varchar(100) not null,
    address varchar(35),
    address2 varchar(35),
    city varchar(21),
    state char(2),
    zip varchar(10),
    main_phone varchar(13),
    fax varchar(13),
    email varchar(128),
    contact_name varchar(50),
    notes text,
    update_counter integer
);

create table person (
    -- primary key
    id_person serial not null primary key,

    -- attributes
    last_name varchar(50),
    first_name varchar(50),
    middle_name varchar(50),
    initials varchar(5),
    address varchar(35),
    city varchar(21),
    state char(2),
    zip varchar(10),
    mailing boolean not null default('false'),
    main_phone varchar(13),
    fax varchar(13),
    email varchar(128),
    CTEC varchar(6),
    previous_employer varchar(50),
    update_counter integer,

    -- constraints
    fk_id_company integer references company(id_company)
);

create table seminar_template (
    -- primary key
    id_seminar_tmpl char(5) not null,
    seminar_tmpl_type varchar(10) not null,

    -- attributes
    seminar_tmpl_name varchar(255) not null,
    notes text,
    update_counter integer,

    -- constraints
    primary key (id_seminar_tmpl, seminar_tmpl_type)
);

create table seminar (
    -- primary key fields
    fk_id_seminar_tmpl char(5),
    fk_seminar_tmpl_type varchar(10),
    seminar_code char(5),
    seminar_year integer,

    -- attributes
    seminar_start_date timestamp not null,
    seminar_end_date timestamp not null,
    shipping_date timestamp,
    billing_date timestamp,
    guarantee_num integer,
    guarantee_date timestamp,
    speaker_id integer references speaker (id_speaker),
    speaker_arrival_date timestamp,
    speaker_no_nights integer,
    speaker_conf_ref varchar(20),
    speaker_rate numeric(10,6),
    facilitator_name varchar(50),
    facilitator_arrival_date timestamp,
    facilitator_no_nights integer,
    facilitator_conf_ref varchar(20),
    notes text,
    fk_id_hotel integer references hotel(id_hotel),
    update_counter integer,

    -- constraints
    primary key (fk_id_seminar_tmpl, fk_seminar_tmpl_type, seminar_code,
seminar_year),
    foreign key (fk_id_seminar_tmpl, fk_seminar_tmpl_type)
        references seminar_template (id_seminar_tmpl, seminar_tmpl_type)
);

create table booking (
    -- primary key
    id_booking serial not null primary key,

    -- business key
    fk_id_seminar_tmpl char(5),
    fk_seminar_tmpl_type varchar(10),
    fk_seminar_code char(5),
    fk_seminar_year integer,
    fk_id_person integer,

    -- attributes
    attended boolean not null default('false'),
    total_price numeric(10,6),
    amount_paid numeric(10,6),
    sale_date timestamp,
    revision_date timestamp,
    fk_id_market_source varchar(50) references market_source (id_market_source),
    quiz boolean not null default('false'),
    shipping_address text,
    ship_date timestamp,
    received_date timestamp,
    score integer,
    cancelled boolean not null default('false'),
    fk_id_booking_transfer integer references booking(id_booking),
    update_counter integer,

    -- constraints
    unique (fk_id_seminar_tmpl, fk_seminar_tmpl_type,
        fk_seminar_code, fk_seminar_year, fk_id_person),
    foreign key (fk_id_seminar_tmpl, fk_seminar_tmpl_type,
        fk_seminar_code, fk_seminar_year)
        references seminar (fk_id_seminar_tmpl, fk_seminar_tmpl_type,
        seminar_code, seminar_year),
    foreign key (fk_id_person) references person (id_person)
);

Tom Ansley <tansley@law.du.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

ok,

everything works.  It worked all along....I was just grabbing the initial
boolean value by mistake.

Thanks all

Tom

On Friday 24 May 2002 12:24 pm, Tom Lane wrote:
> Tom Ansley <tansley@law.du.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