Solved! I think (Re: select distinct on computed column)

From: Alvin Sylvain <alvin_at_c-square.no.junk>
Date: 1997/01/08
Message-ID: <32D40E97.18AE@c-square.no.junk>#1/1

Jim Smith wrote:
>
> In article <32D2C414.1A93_at_c-square.no.junk>, Alvin Sylvain <alvin_at_c-
> square.no.junk> writes
> >I'm converting an application from Sybase to Oracle and I've run
> >into a number of quandries.
> >
> >This one is performing a "distinct" select on a "computed" column.
> >
> >Eg:
> >
> > select distinct name || ' ' || type "nametype"
> > from categorytable
> >
> >Sybase allowed this, altho Oracle seems to not.
>
> What error are you getting? This is perfectly valid.

For those who might have missed my earlier posting, the error was 1791, not a SELECTed expression.

As it turns out, the view was joining about 4 tables, but the upshot was that the columns being selected were themselves computed string concatenations.

Eg, the view read something like this:

    create view personview ( alias, name ) as

    select p.personnelalias,
           p.lastname || ', ' || p.firstname || ' '
           p.middleinitial
    from personnel p, company c, etc. etc.
      where etc. etc. etc.

Essentially the original view was trying to cull from the entire personnel table only the people in a specific set of companies, and only those with non-zero account balances, plus do a little bit of "user-friendly" formatting as well.

My failing query read something like the following:

    select distinct alias || ' ' || name "thename"     from personview

and received the 1791 error. W/O the "distinct" it was kool.

I fixed the problem by creating a separate view which reads like the following:

    create view personview1 ( thename ) as     select distinct p.personnelalias || ' '

           p.lastname || ', ' || p.firstname || ' '
           p.middleinitial

    from personnel p, company c, etc. etc.

Then I just use the new view instead of the old view:

    select thename from personview1

I don't know if this is the best solution, or if maybe there's a problem Oracle should know about (again, Sybase has no heartburn with it at all), but it is =a= solution and it's quick and easy.

Thanks to all who helped!!

Alvin

P.S. I still don't know how to view the contents of a view. "select text from user_views where view_name = 'VIEW_NAME'" stops after about 100 or so characters. The only reason I had any clue to this view was the fact that I still have the old Sybase version. Received on Wed Jan 08 1997 - 00:00:00 CST