pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCASE
20+ messages / 12 participants
[nested] [flat]
* CASE
@ 1999-05-03 07:55 vernichon eric <e.vernichon@borchers.fr>
1999-05-03 12:43 ` Re: [SQL] CASE José Soares <jose@sferacarta.com>
0 siblings, 1 reply; 20+ messages in thread
From: vernichon eric @ 1999-05-03 07:55 UTC (permalink / raw)
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
Hello ,
I want transfered query from sql serveur to postgres but i
don't know how use CASE with postgres
Thank you
Eric Vernichon
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: [SQL] CASE
1999-05-03 07:55 CASE vernichon eric <e.vernichon@borchers.fr>
@ 1999-05-03 12:43 ` José Soares <jose@sferacarta.com>
0 siblings, 0 replies; 20+ messages in thread
From: José Soares @ 1999-05-03 12:43 UTC (permalink / raw)
To: vernichon eric <e.vernichon@borchers.fr>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
You need v6.5beta, previous releases haven't this feature:
SELECT *, CASE
WHEN COLUMN1 = 1
THEN 'A'
WHEN COLUMN1 = 2
THEN 'B'
ELSE 'C'
END FROM TABLE2;
column1|case
-------+----
1|A
2|B
3|C
4|C
(4 rows)
José
vernichon eric ha scritto:
> Hello ,
>
> I want transfered query from sql serveur to postgres but i
> don't know how use CASE with postgres
>
> Thank you
>
> Eric Vernichon
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 20+ messages in thread
* Case
@ 2001-05-14 15:21 Cwhisperer <Cwhisperer@vo.lu>
2001-05-14 20:39 ` Re: Case Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 20+ messages in thread
From: Cwhisperer @ 2001-05-14 15:21 UTC (permalink / raw)
To: pgsql-sql
Hello,
what's wrong when you receive this message from an select statement
: CASE types "date" and "text" not matched
thank you for any help
--
Best regards,
Cwhisperer mailto:Cwhisperer@vo.lu
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Case
2001-05-14 15:21 Case Cwhisperer <Cwhisperer@vo.lu>
@ 2001-05-14 20:39 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 20+ messages in thread
From: Peter Eisentraut @ 2001-05-14 20:39 UTC (permalink / raw)
To: Cwhisperer <Cwhisperer@vo.lu>; +Cc: pgsql-sql
Cwhisperer writes:
> what's wrong when you receive this message from an select statement
> : CASE types "date" and "text" not matched
You probably did something like
CASE WHEN something THEN date_value ELSE text_value
The data types in the result branches of a case expression need to match.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
^ permalink raw reply [nested|flat] 20+ messages in thread
* CASE
@ 2003-04-08 08:29 Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
0 siblings, 1 reply; 20+ messages in thread
From: Rudi Starcevic @ 2003-04-08 08:29 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
@ 2003-04-08 09:40 ` Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 10:57 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 20+ messages in thread
From: Victor Yegorov @ 2003-04-08 09:40 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
@ 2003-04-08 10:57 ` Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 11:09 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 1 reply; 20+ messages in thread
From: Rudi Starcevic @ 2003-04-08 10:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 10:57 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
@ 2003-04-08 11:09 ` Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 11:20 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 1 reply; 20+ messages in thread
From: Victor Yegorov @ 2003-04-08 11:09 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
* 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 10:57 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 11:09 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
@ 2003-04-08 11:20 ` Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 0 replies; 20+ messages in thread
From: Rudi Starcevic @ 2003-04-08 11:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
@ 2003-04-08 12:06 ` Greg Stark <gsstark@mit.edu>
2003-04-08 12:13 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 14:30 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 20+ messages in thread
From: Greg Stark @ 2003-04-08 12:06 UTC (permalink / raw)
To: Victor Yegorov <viy@pirmabanka.lv>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
"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 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:
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
@ 2003-04-08 12:13 ` Victor Yegorov <viy@pirmabanka.lv>
1 sibling, 0 replies; 20+ messages in thread
From: Victor Yegorov @ 2003-04-08 12:13 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
* 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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
@ 2003-04-08 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-08 23:13 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
1 sibling, 1 reply; 20+ messages in thread
From: Tom Lane @ 2003-04-08 14:30 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Victor Yegorov <viy@pirmabanka.lv>; Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
2003-04-08 14:30 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-08 23:13 ` Rudi Starcevic <rudi@oasis.net.au>
2003-04-09 04:47 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Rudi Starcevic @ 2003-04-08 23:13 UTC (permalink / raw)
To: pgsql-sql
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@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, '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@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\'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@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', '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);
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
2003-04-08 14:30 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
2003-04-08 23:13 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
@ 2003-04-09 04:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-09 04:59 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2003-04-09 04:47 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Re: CASE Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 12:06 ` Re: CASE Greg Stark <gsstark@mit.edu>
2003-04-08 14:30 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
2003-04-08 23:13 ` Re: CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-09 04:47 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-04-09 04:59 ` Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 0 replies; 20+ messages in thread
From: Rudi Starcevic @ 2003-04-09 04:59 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* CASE
@ 2003-05-19 06:59 James Taylor <jtx@hatesville.com>
2003-05-19 22:49 ` Re: CASE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-19 23:24 ` Re: CASE Rod Taylor <rbt@rbt.ca>
2003-05-19 23:52 ` Re: CASE Manfred Koizar <mkoi-pg@aon.at>
2003-05-20 00:17 ` Re: CASE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 20+ messages in thread
From: James Taylor @ 2003-05-19 06:59 UTC (permalink / raw)
To: pgsql-sql
Hi everyone, I'm trying to figure out how to get CASE working in a sum
aggregate function. Pretty simple query:
select sum(numbers) from lists where uid=1;
It's possible that there may be 0 rows where uid == 1. Instead of
returning 1 blank row, I want it to return '0'. So, I tried this:
select case(sum(numbers)) when null then 0 else sum(numbers) end from
list_results;
That... Doesn't work. Any suggestions on how to do this and where I can
find some decent information on use of CASE?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-05-19 06:59 CASE James Taylor <jtx@hatesville.com>
@ 2003-05-19 22:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 0 replies; 20+ messages in thread
From: Stephan Szabo @ 2003-05-19 22:49 UTC (permalink / raw)
To: James Taylor <jtx@hatesville.com>; +Cc: pgsql-sql
On Sun, 18 May 2003, James Taylor wrote:
> Hi everyone, I'm trying to figure out how to get CASE working in a sum
> aggregate function. Pretty simple query:
>
> select sum(numbers) from lists where uid=1;
>
> It's possible that there may be 0 rows where uid == 1. Instead of
> returning 1 blank row, I want it to return '0'. So, I tried this:
>
> select case(sum(numbers)) when null then 0 else sum(numbers) end from
> list_results;
I believe the case would be:
case when sum(numbers) is null then 0 else sum(numbers) end
It's more like the ternary operator than a switch statement.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-05-19 06:59 CASE James Taylor <jtx@hatesville.com>
@ 2003-05-19 23:24 ` Rod Taylor <rbt@rbt.ca>
3 siblings, 0 replies; 20+ messages in thread
From: Rod Taylor @ 2003-05-19 23:24 UTC (permalink / raw)
To: jtx@hatesville.com; +Cc: pgsql-sql
On Mon, 2003-05-19 at 02:59, James Taylor wrote:
> Hi everyone, I'm trying to figure out how to get CASE working in a sum
> aggregate function. Pretty simple query:
>
> select sum(numbers) from lists where uid=1;
>
> It's possible that there may be 0 rows where uid == 1. Instead of
> returning 1 blank row, I want it to return '0'. So, I tried this:
>
> select case(sum(numbers)) when null then 0 else sum(numbers) end from
> list_results;
Use Coalesce:
SELECT coalesce(sum(numbers), 0) FROM lists WHERE uid = 1;
To use case, efficiently, if you really want:
SELECT CASE WHEN sum_numbers IS NULL THEN 0
ELSE sum_numbers
END AS sum_numbers
FROM (SELECT sum(numbers) AS sum_numbers
FROM lists
WHERE uid = 1
) as tab;
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-05-19 06:59 CASE James Taylor <jtx@hatesville.com>
@ 2003-05-19 23:52 ` Manfred Koizar <mkoi-pg@aon.at>
3 siblings, 0 replies; 20+ messages in thread
From: Manfred Koizar @ 2003-05-19 23:52 UTC (permalink / raw)
To: jtx@hatesville.com; +Cc: pgsql-sql
On Sun, 18 May 2003 23:59:46 -0700, "James Taylor"
<jtx@hatesville.com> wrote:
>select case(sum(numbers)) when null then 0 else sum(numbers) end from
>list_results;
>
>That... Doesn't work.
SELECT CASE WHEN SUM(numbers) IS NULL THEN 0 ELSE SUM(numbers) END ...
or
SELECT COALESCE(SUM(numbers), 0) FROM ...
Servus
Manfred
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: CASE
2003-05-19 06:59 CASE James Taylor <jtx@hatesville.com>
@ 2003-05-20 00:17 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2003-05-20 00:17 UTC (permalink / raw)
To: jtx@hatesville.com; +Cc: pgsql-sql
"James Taylor" <jtx@hatesville.com> writes:
> select case(sum(numbers)) when null then 0 else sum(numbers) end from
> list_results;
The reason the above doesn't work is that it expands to
CASE WHEN sum(numbers) = null THEN ...
which always fails ("= null" does not mean "is null").
Easier would be SELECT COALESCE(sum(numbers), 0) FROM list_results
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2003-05-20 00:17 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-03 07:55 CASE vernichon eric <e.vernichon@borchers.fr>
1999-05-03 12:43 ` José Soares <jose@sferacarta.com>
2001-05-14 15:21 Case Cwhisperer <Cwhisperer@vo.lu>
2001-05-14 20:39 ` Re: Case Peter Eisentraut <peter_e@gmx.net>
2003-04-08 08:29 CASE Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 09:40 ` Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 10:57 ` Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 11:09 ` Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 11:20 ` Rudi Starcevic <rudi@oasis.net.au>
2003-04-08 12:06 ` Greg Stark <gsstark@mit.edu>
2003-04-08 12:13 ` Victor Yegorov <viy@pirmabanka.lv>
2003-04-08 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-08 23:13 ` Rudi Starcevic <rudi@oasis.net.au>
2003-04-09 04:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-09 04:59 ` Rudi Starcevic <rudi@oasis.net.au>
2003-05-19 06:59 CASE James Taylor <jtx@hatesville.com>
2003-05-19 22:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-19 23:24 ` Rod Taylor <rbt@rbt.ca>
2003-05-19 23:52 ` Manfred Koizar <mkoi-pg@aon.at>
2003-05-20 00:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox