Обсуждение: CASE

Hi,

I'm trying to find the maximum date then compare that date to see if I 
have any current events to show.
Here is an SQL snippet ( the e.edate field is of type 'date' )

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.

I can't see any problem right now can anyone here see one ?

Thank you kindly
Regards
Rudi.

* Rudi Starcevic <rudi@oasis.net.au> [08.04.2003 11:37]:
> CASE
>    WHEN COALESCE( MAX(e.edate),'2003-01-01') >= now()::date THEN 'events'
>    ELSE 'noevents'
> END as myevents,

I'm not sure completely, but first thing i saw in your query, is:

...COALESCE( MAX(e.edate),'2003-01-01')...

MAX(e.edate): will return date type (just a guess)
'2003-01-01': will return char type (or varchar, doesn't matter).

Try to cast string into date.

--

Victor Yegorov

Victor,

Thanks for your time ..

When casting inside a COALESCE function should it be something like:

WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >= now()::date THEN 'events'

instead of:

WHEN COALESCE( MAX(e.edate),'2003-01-01') >= now()::date THEN 'events'

Regards
Rudi

* Rudi Starcevic <rudi@oasis.net.au> [08.04.2003 14:06]:
> When casting inside a COALESCE function should it be something like:
>
> WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >= now()::date THEN 'events'

Yes.
Please, try it and report if it works.

--

Victor Yegorov

Victor,

Thanks again,

I'll try first thing in the morning as I'm at home now.
Now I can sleep well knowing a solution is at hand :-)

I'm very pleased as that was a small part of what I think is
an advanced query and look forward to seeing it work.

I'll send a reply first thing tomorrow when I get to work.
Cheers
Rudi.

> * Rudi Starcevic <rudi@oasis.net.au> [08.04.2003 14:06]:
> > When casting inside a COALESCE function should it be something like:
> > 
> > WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >= now()::date THEN 'events'
> 
> Yes.
> Please, try it and report if it works.
> 
> -- 
> 
> Victor Yegorov

"Victor Yegorov" <viy@pirmabanka.lv> writes:

> ...COALESCE( MAX(e.edate),'2003-01-01')...
> 
> MAX(e.edate): will return date type (just a guess)
> '2003-01-01': will return char type (or varchar, doesn't matter).

No, 'xyz' in postgres isn't necessarily a string constant, it's an untyped
constant that the parser decides the type of based on context. In this case it
gets decided based on the other return values of the coalesce/case.

Notice the types chosen in this query:

db=# explain select * from t where t_start =  coalesce(now(),'2003-01-01') ;
                                                           QUERY PLAN
                                                      
 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon t  (cost=0.00..48223.40 rows=226 width=423)  Filter: ((t_start)::timestamp with time zone = CASE WHEN (now() IS
NOTNULL) THEN now() WHEN ('2003-01-01' IS NOT NULL) THEN '2003-01-01 00:00:00-05'::timestamp with time zone ELSE
NULL::timestampwith time zone END)
 
(2 rows)


However, I suspect for some reason postgres is doing a string comparison in
your case. I don't understand why. Note that now() isn't a date, it's a
"timestamp with time zone", but I don't think that should be a problem. 
Try checking what types it uses in the plan for:

EXPLAIN SELECT *  FROM e WHERE (CASE WHEN coalesce(max(e.edate),'2003-01-01') >= now()::date             THEN 'events'
          ELSE 'noevents'              END      ) = 'events';
 



Also, the standard date format should still sort correctly as alphanumeric, 
so I'm curious what you see when you do:

SELECT now();

--
greg

* Greg Stark <gsstark@mit.edu> [08.04.2003 15:07]:
>
> "Victor Yegorov" <viy@pirmabanka.lv> writes:
>
> > ...COALESCE( MAX(e.edate),'2003-01-01')...
> >
> > MAX(e.edate): will return date type (just a guess)
> > '2003-01-01': will return char type (or varchar, doesn't matter).
>
> No, 'xyz' in postgres isn't necessarily a string constant, it's an untyped
> constant that the parser decides the type of based on context. In this case it
> gets decided based on the other return values of the coalesce/case.

Yes, yes. I know that. This is what I ment by saing `I'm not sure' in the
first reply.

>
> Notice the types chosen in this query:
>
> db=# explain select * from t where t_start =  coalesce(now(),'2003-01-01') ;
>                                                                                                          QUERY PLAN
                                                                                                       
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on t  (cost=0.00..48223.40 rows=226 width=423)
>    Filter: ((t_start)::timestamp with time zone = CASE WHEN (now() IS NOT NULL) THEN now() WHEN ('2003-01-01' IS NOT
NULL)THEN '2003-01-01 00:00:00-05'::timestamp with time zone ELSE NULL::timestamp with time zone END) 
> (2 rows)
>
>
> However, I suspect for some reason postgres is doing a string comparison in
> your case. I don't understand why. Note that now() isn't a date, it's a
> "timestamp with time zone", but I don't think that should be a problem.
> Try checking what types it uses in the plan for:

To avoid string comparison I've suggested to cast into date format
manually.

Actually, I have never came across with such situation, so I don't know the
exact solution.

--

Victor Yegorov

Greg Stark <gsstark@mit.edu> writes:
> However, I suspect for some reason postgres is doing a string comparison in
> your case. I don't understand why.

I'm wondering what the data type of e.edate actually is...
        regards, tom lane

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'
ANDe.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
DEFAULTnow() 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@wilbur.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@ramjet.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,
'RamjetBar', '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\"><palign=\"center\" class=\"textW\"><b>Trading Hours</b></p></td></tr><tr><td> </td><td
bgcolor=\"#666666\"><pclass=\"textW\"><b>open</b></p></td><td bgcolor=\"#666666\"><p
class=\"textW\"><b>close</b></p></td></tr><tr><tdbgcolor=\"#666666\" class=\"textW\">Sunday</td><td
bgcolor=\"#666666\"><pclass=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p
class=\"textW\">12pm.</p></td></tr><tr><tdbgcolor=\"#666666\" class=\"textW\">Monday</td><td bgcolor=\"#666666\"><p
class=\"textW\">12am.</p></td><tdbgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\"
class=\"textW\">Tuesday</td><tdbgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p
class=\"textW\">12pm.</p></td></tr><tr><tdbgcolor=\"#666666\" class=\"textW\">Wednesday</td><td class=\"textW\"
bgcolor=\"#666666\"><p>12am.</p></td><tdbgcolor=\"#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\"><pclass=\"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\"><pclass=\"textW\">12pm.</p></td></tr></table>', '37 Bundall Road', 2723, 'Surfers Paradise', 4217,
'GoldCoast', 'QLD', '54454545', '54455454', 'rudi@who.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\'sPlace', '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\"><pclass=\"textW\"><b>close</b></p></td></tr><tr><td bgcolor=\"#666666\"
class=\"textW\">Sunday</td><tdbgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p
class=\"textW\">12pm.</p></td></tr><tr><tdbgcolor=\"#666666\" class=\"textW\">Monday</td><td bgcolor=\"#666666\"><p
class=\"textW\">12am.</p></td><tdbgcolor=\"#666666\"><p class=\"textW\">12pm.</p></td></tr><tr><td bgcolor=\"#666666\"
class=\"textW\">Tuesday</td><tdbgcolor=\"#666666\"><p class=\"textW\">12am.</p></td><td bgcolor=\"#666666\"><p
class=\"textW\">12pm.</p></td></tr><tr><tdbgcolor=\"#666666\" class=\"textW\">Wednesday</td><td class=\"textW\"
bgcolor=\"#666666\"><p>12am.</p></td><tdbgcolor=\"#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\"><pclass=\"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\"><pclass=\"textW\">12pm.</p></td></tr></table>', '6 Elliot Street', 2715, 'Chevron Island', 4217,
'GoldCoast', 'QLD', '54545454', '', 'wally@wilbur.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',
'asdfsadf 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',
'Pokercard 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); 

Rudi Starcevic <rudi@oasis.net.au> writes:
> However there is one event which is in the past '2003-04-01' which is 
> still coming up as 'events' instead of 'noevents'.

But you're grouping over members and venues, and taking the max across
event dates in each group.  A past event for a venue won't affect
anything, if there are later events for that venue.
        regards, tom lane

Tom,

I see ..
Thanks.

I'll need to rewrite my query.
I'm not sure I understand right now so it will be a good challenge for me ..

Any tips appreciated :-)

Cheers
Rudi.

Tom Lane wrote:

>Rudi Starcevic <rudi@oasis.net.au> writes:
>  
>
>>However there is one event which is in the past '2003-04-01' which is 
>>still coming up as 'events' instead of 'noevents'.
>>    
>>
>
>But you're grouping over members and venues, and taking the max across
>event dates in each group.  A past event for a venue won't affect
>anything, if there are later events for that venue.
>
>            regards, tom lane
>
>  
>