Re: Using CASE with a boolean value - Mailing list pgsql-novice

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