Re: Using CASE with a boolean value

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)
);