From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: "Subquery must return only one column" & query optimization |
Date: | 2008-12-15 14:21:41 |
Message-ID: | E6A0649F1FBFA3408A37F505400E7AC2118534@email.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
pgsql-sql-owner(at)postgresql(dot)org wrote:
> "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
>> I was trying to run this query this morning:
>
>> --------------------------
>> SELECT
>
>> r.*,
>
>> (
>> SELECT
>
>> rl.reminder_header,
>> rl.reminder_footer
>
>> FROM reminder_levels AS rl
>> WHERE rl.lookup =
>> (
>> SELECT MAX(reminder_level_lookup)
>> FROM reminders
>> WHERE customer_id = r.customer_id
>> )
>> )
>
>> FROM reminders AS r
>> --------------------------
>
>> Postgresql replied that:
>
>> --------------------------
>> ERROR: subquery must return only one column
>
> Since 8.0 or so you could write the sub-select as
>
> SELECT ROW(rl.reminder_header, rl.reminder_footer) FROM ...
>
> We ought to make that happen automatically, but it's not real high on
> the to-do list.
Hi Tom,
Fine, the query is faster now:
------------------------
SELECT
r.*,
(
SELECT
ROW(rl.reminder_header, rl.reminder_header)
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup)
FROM reminders
WHERE customer_id = r.customer_id
)
) AS rec
FROM reminders AS r
------------------------
... but the last column is now of type "record", and since this query
serves as a datasource for a MS Access report, it is not able to "split"
the record into the values that interest me: reminder_header and
reminder_footer!
Regards,
Philippe Lang
From | Date | Subject | |
---|---|---|---|
Next Message | Marco Lechner | 2008-12-15 21:42:15 | create table with rownames as values in column of seciond table |
Previous Message | Tom Lane | 2008-12-15 13:17:57 | Re: "Subquery must return only one column" & query optimization |