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@debis.com (Jan Wieck) #