pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFailing query...
2+ messages / 2 participants
[nested] [flat]
* Failing query...
@ 2000-08-19 05:53 Shaun <delius@progsoc.uts.edu.au>
0 siblings, 1 reply; 2+ messages in thread
From: Shaun @ 2000-08-19 05:53 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I was hoping someone here would be able to help me with a query I have
that is failing with the error:
psql:query:1: ERROR: ExecEvalExpr: unknown expression type 501
The query is:
SELECT a.auction_id, a.user_id, c.other_names,
c.surname, c.email, a.reserve, a.close_time, a.short_desc,
a.long_desc, a.start_time,
(COALESCE((select MAX(bid) from bid where auction_id = a.auction_id
group by auction_id), 0)) as max_bid
FROM Auction a, Customer c
WHERE a.user_id = c.user_id
AND a.auction_id = 754;
If I run the following however,
SELECT a.auction_id, a.user_id, c.other_names,
c.surname, c.email, a.reserve, a.close_time, a.short_desc,
a.long_desc, a.start_time
FROM Auction a, Customer c
WHERE a.user_id = c.user_id
AND a.auction_id = 754;
I get:
auction_id | user_id | other_names | surname | email
|
reserve | close_time | short_desc |
long_de
sc | start_time
------------+---------+-------------+-----------+-----------------------------+-
---------+------------------------+-----------------------+---------------------
----------------+------------------------
754 | 4 | raymond | villarica |
ray@woodstock.tabnsw.com.au |
22000.00 | 2000-08-17 17:12:16+10 | 8 Avona Av Glebe 2037 | other
residential 70
8 square metres | 1990-01-01 00:00:00+11
(1 row)
If I run:
select MAX(bid) from bid where auction_id = 754;
I get:
max
------------
3100000.00
(1 row)
Can someone please help me understand what I'm doing wrong. If there is no
value in the select max() subselect, the query works, but if there is, it
doesn't.
Please CC: me to any reply as I've emailed to join the list but the reply
hasn't yet reached me from majordomo.
Thanks,
Shaun
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Failing query...
@ 2000-08-19 21:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Shaun <delius@progsoc.uts.edu.au>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-08-19 21:34 UTC (permalink / raw)
To: Shaun <delius@progsoc.uts.edu.au>; +Cc: pgsql-sql
Shaun <delius@progsoc.uts.edu.au> writes:
> SELECT a.auction_id, a.user_id, c.other_names,
> c.surname, c.email, a.reserve, a.close_time, a.short_desc,
> a.long_desc, a.start_time,
> (COALESCE((select MAX(bid) from bid where auction_id = a.auction_id
> group by auction_id), 0)) as max_bid
> FROM Auction a, Customer c
> WHERE a.user_id = c.user_id
> AND a.auction_id = 754;
Sub-selects inside COALESCE don't work :-(. This was just fixed about a
week ago --- it will be in 7.1. In the meantime you might try it the
other way round:
(select COALESCE(MAX(bid), 0) from bid where auction_id = a.auction_id)
as max_bid
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-08-19 21:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-19 05:53 Failing query... Shaun <delius@progsoc.uts.edu.au>
2000-08-19 21:34 ` 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