Thread: subselects & views

select max(ccorderseq) from ccorder;
max
---
603
(1 row)

icv=> select * From the_view where ccorderseq = 603;

[ I get the row back ]

select * From the_view where ccorderseq = (select max(ccorderseq) from ccorder);

I don't get the row back.  nothing has changed, the max value is still
the same.  when I write out the view as a select, the subquery works
and I get the row.

if it makes much of a difference the view is doing a "glob" query (of
a parent and all children, i.e. select from table*)

I think this is fixed in 6.4.  Beta is September 1.

>
> select max(ccorderseq) from ccorder;
> max
> ---
> 603
> (1 row)
>
> icv=> select * From the_view where ccorderseq = 603;
>
> [ I get the row back ]
>
> select * From the_view where ccorderseq = (select max(ccorderseq) from ccorder);
>
> I don't get the row back.  nothing has changed, the max value is still
> the same.  when I write out the view as a select, the subquery works
> and I get the row.
>
> if it makes much of a difference the view is doing a "glob" query (of
> a parent and all children, i.e. select from table*)
>
>
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)