Re: [SQL] More view problems

>
> Building on DeJuan's suggestion, we have arrived at the following code
> (using the extra view because from queries don't seem to allow sub
> queries):
>
> create view tab021temp as select fldkey as fldkey2, fld000 as fld000,
> fld001 as fld001 from tab000 group by fld001, fld000, fldkey2;
>
> create view tab021 as select count(1) as fldkey, a.fldkey2, a.fld000,
> a.fld001 from tab021temp a, tab021temp b where a.fld001 > b.fld001 or
> (a.fld001 = b.fld001 and a.fld000 > b.fld000) or a.fld000 = b.fld000
> grouPQexec() -- Request was sent to backend, but backend closed the
> channel before rp by fld001, fld000, fldkey2;
>
> select * from tab021;
>
> This terminates in psql with the following error after the select
> statement is made:
>
> PQexec() -- Request was sent to backend, but backend closed the channel
> before r
> esponding. This probably means the backend terminated abnormally before
> or while pr ocessing the request.
>
> What is going on here? Multi level views seem to work if they are
> simple. Why is the connection being dropped? JDBC gives up a similar
> broken pipe error.

There are still bugs in the rewrite system about handling of
GROUP BY in views. It is because views aren't implemented in
a form of subselect. Instead they replace expressions in the
original query. I don't want to explain it in detail here
(RTF-programmers-M). However deep you nest views, the final
rewritten statement is still something you could have typed
as a legal SQL statement - and such has only one possible
grouping.

Don't use GROUP BY in view definitions for now.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #