Re: CASE

Hi,

Thanks for the replys ..
I'm still having a little trouble with my CASE/COALESCE SQL though.
This time I provide more information.

First the problem again.

I'm trying to find the maximum date then compare that date to see if I
have any current events to show.

CASE
WHEN COALESCE( MAX(e.edate),'2003-01-01') >= now()::date THEN 'events'
ELSE 'noevents'
END as myevents,

It works fine for those events who do have dates greater or equal to now and
also for those events who have a NULL value.

However there is one event which is in the past '2003-04-01' which is
still coming up as 'events' instead of 'noevents'.
I think there may be something wrong with my CASE SQL clause.

From the sql and data below event_id number 15, which has a date in the
past, is coming
up for me as 'events' instead of 'noevents'.
Sorry but I can't see why.
Some help much appreciated.

Kind regards
Rudi.

Second the sql and some dummy data.

SELECT m.m_id, m.active,
v.*,

CASE
WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >=
now()::date THEN 'events'
ELSE 'noevents'
END AS myevents, -- this is 22 in group by clause

p.v_id AS photo_v_id, COALESCE(p.purl,'') AS photourl
FROM
members m,
events e,
venues v
LEFT OUTER JOIN photos p ON
(
p.pactive = 't'
AND
v.v_id = p.v_id
)
WHERE m.m_id = v.m_id
AND
m.active = 't'
AND
v.vactive = 't'
AND e.v_id = v.v_id
GROUP BY
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24

CREATE TABLE members
(
m_id serial PRIMARY KEY,
title varchar(8) NOT NULL,
fname varchar(60) NOT NULL,
lname varchar(80) NOT NULL,
address_1 varchar(200) NOT NULL,
suburb varchar(80) NOT NULL,
town varchar(60) NOT NULL,
pcode integer NOT NULL,
state varchar(8) NOT NULL,
phone varchar(20) NOT NULL,
fax varchar(20),
email varchar(160) UNIQUE NOT NULL,
pwd varchar(8) NOT NULL,
create_date date DEFAULT now() NOT NULL,
update_date date DEFAULT now() NOT NULL,
active boolean DEFAULT 'f'::bool NOT NULL,
seqnum integer NOT NULL
);

CREATE TABLE venues
(
v_id serial PRIMARY KEY,
m_id integer REFERENCES members ON UPDATE CASCADE ON DELETE CASCADE NOT
NULL,
vname varchar(200) NOT NULL,
vdesc text NOT NULL,
vtype varchar(40) NOT NULL,
vhours text NOT NULL,
vaddress_1 varchar(180) NOT NULL,
vsub_id integer NOT NULL,
vsuburb varchar(80) NOT NULL,
vpcode integer NOT NULL,
vtown varchar(60) NOT NULL,
vstate varchar(8) NOT NULL,
vphone varchar(20) NOT NULL,
vfax varchar(20),
vemail varchar(160) NOT NULL,
vurl varchar(180),
vcreate_date date DEFAULT now() NOT NULL,
vupdate_date date DEFAULT now() NOT NULL,
vactive boolean DEFAULT 'f'::bool NOT NULL
);

CREATE TABLE events (
e_id serial PRIMARY KEY,
v_id int4 REFERENCES venues ON UPDATE CASCADE ON DELETE CASCADE
INITIALLY DEFERRED NOT NULL,
ename varchar(160),
edesc text,
edate date,
edoorsopen varchar(12),
etype varchar(60)
);

CREATE TABLE photos (
p_id serial PRIMARY KEY,
v_id int4 REFERENCES venues ON UPDATE CASCADE ON DELETE CASCADE NOT
NULL,
pdesc text NOT NULL,
purl text NOT NULL,
pcreate_date date DEFAULT now() NOT NULL,
pupdate_date date DEFAULT now() NOT NULL,
pactive boolean DEFAULT 'f'::bool NOT NULL,
seqnum integer NOT NULL
);

INSERT INTO "members" ("m_id", "title", "fname", "lname", "address_1", "suburb", "town", "pcode", "state", "phone", "fax", "email", "pwd", "create_date", "update_date", "active", "seqnum") VALUES(3, 'Mr.', 'Wally', 'Wilbur', '6 Elliot Street', 'Bundall', 'Gold Coast', 4217, 'QLD', '54545454', '', 'wally(at)wilbur(dot)com', '2222', '2003-04-08', '2003-04-08', 't', 1004);
INSERT INTO "members" ("m_id", "title", "fname", "lname", "address_1", "suburb", "town", "pcode", "state", "phone", "fax", "email", "pwd", "create_date", "update_date", "active", "seqnum") VALUES(1, 'Mr.', 'Roger', 'Ramjet', '37 Bundall Road', 'Bundall', 'Gold Coast', 4217, 'QLD', '54454545', '54455454', 'rojer(at)ramjet(dot)com', '2222', '2003-04-08', '2003-04-08', 't', 1026);

INSERT INTO "venues" ("v_id", "m_id", "vname", "vdesc", "vtype", "vhours", "vaddress_1", "vsub_id", "vsuburb", "vpcode", "vtown", "vstate", "vphone", "vfax", "vemail", "vurl", "vcreate_date", "vupdate_date", "vactive") VALUES(1, 1, 'Ramjet Bar', 'Public Bar in the heart of Surfer's Paradise with happy hour for Proton Pills', 'Public Bar', '<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"3\" style=\"border: 1px solid black\"><tr><td colspan=\"3\"><p align=\"center\" class=\"textW\"><b>Trading Hours</b></p></td></tr><tr><td> </td><td bgcolor=\"#666666\"><p class=\"textW\"><b>open</b></p></td><td bgcolor=\"#666666\"><p class=\"textW\"><b>close</b></p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Sunday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Monday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Tuesday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Wednesday</td><td class=\"textW\" bgcolor=\"#666666\"><p>12am.</p></td><td bgcolor=\"#666666\" class=\"textW\"><p>12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Thursday</td><td class=\"textW\" bgcolor=\"#666666\"><p>12am.</p></td><td class=\"textW\" bgcolor=\"#666666\"><p>12pm.</p></td></tr><tr bgcolor=\"#666666\" class=\"textW\"><td>Friday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr bgcolor=\"#666666\" class=\"textW\"><td>Saturday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr></table>', '37 Bundall Road', 2723, 'Surfers Paradise', 4217, 'Gold Coast', 'QLD', '54454545', '54455454', 'rudi(at)who(dot)net', 'http://www.', '2003-04-08', '2003-04-08', 't');
INSERT INTO "venues" ("v_id", "m_id", "vname", "vdesc", "vtype", "vhours", "vaddress_1", "vsub_id", "vsuburb", "vpcode", "vtown", "vstate", "vphone", "vfax", "vemail", "vurl", "vcreate_date", "vupdate_date", "vactive") VALUES(3, 3, 'Wally\'s Place', 'Free Beer at Wally's every day', 'Public Bar', '<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"3\" style=\"border: 1px solid black\"><tr><td colspan=\"3\"><p align=\"center\" class=\"textW\"><b>Trading Hours</b></p></td></tr><tr><td> </td><td bgcolor=\"#666666\"><p class=\"textW\"><b>open</b></p></td><td bgcolor=\"#666666\"><p class=\"textW\"><b>close</b></p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Sunday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Monday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Tuesday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Wednesday</td><td class=\"textW\" bgcolor=\"#666666\"><p>12am.</p></td><td bgcolor=\"#666666\" class=\"textW\"><p>12pm.</p></td></tr><tr><td bgcolor=\"#666666\" class=\"textW\">Thursday</td><td class=\"textW\" bgcolor=\"#666666\"><p>12am.</p></td><td class=\"textW\" bgcolor=\"#666666\"><p>12pm.</p></td></tr><tr bgcolor=\"#666666\" class=\"textW\"><td>Friday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr bgcolor=\"#666666\" class=\"textW\"><td>Saturday</td><td bgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr></table>', '6 Elliot Street', 2715, 'Chevron Island', 4217, 'Gold Coast', 'QLD', '54545454', '', 'wally(at)wilbur(dot)com', '', '2003-04-08', '2003-04-08', 't');

INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(1, 1, 'Full Moon Rave', 'asdf sadf sdf sdf dsf sdf sdfsdf sdf sd;lfj dflj fljsd flsdkjf dsfd sklfjdsfl ksdfklj dlfjds sdjf dskljf s', '2003-04-12', '11pm.', 'Rave');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(2, 1, 'Bridge Challenge', 'Teams of 2 challenge each other for big prizes. All Welcome.', '2003-04-10', '7pm.', 'Card night');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(3, 1, 'Monica and the Moochers', 'Monica and the Moochers Blues Band. Check them out', '2003-04-17', '7pm.', 'Band');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(4, 1, '', '', '2003-04-08', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(5, 1, '', '', '2003-04-08', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(6, 1, '', '', '2003-04-08', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(7, 1, '', '', '2003-04-08', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(15, 3, 'Poker Night', 'Poker card night. All welcome', '2003-04-05', '7pm.', 'Card night');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(16, 3, '', '', '2003-04-09', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(17, 3, '', '', '2003-04-09', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(18, 3, '', '', '2003-04-09', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(19, 3, '', '', '2003-04-09', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(20, 3, '', '', '2003-04-09', '', '');
INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsopen", "etype") VALUES(21, 3, '', '', '2003-04-09', '', '');

INSERT INTO "photos" ("p_id", "v_id", "pdesc", "purl", "pcreate_date", "pupdate_date", "pactive", "seqnum") VALUES(1, 1, 'Ramjet Bar', 'http://www.ozpubsclubs.com.au/ozimages/bilbos-pub.jpeg', '2003-04-08', '2003-04-08', 't', 1026);
INSERT INTO "photos" ("p_id", "v_id", "pdesc", "purl", "pcreate_date", "pupdate_date", "pactive", "seqnum") VALUES(2, 3, 'Wally\'s Place', 'http://www.ozpubsclubs.com.au/ozimages/bilbos-pub.bmp', '2003-04-08', '2003-04-08', 't', 1004);