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

Charles Jardine wrote:
>
> Alvin Sylvain wrote:
>
> > 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.
>
> We might all get somewhere if you would post the *actual*
> failing query, rather than 'something like' it.

Actually, I'm getting plenty of information without. The problem is, a) the view is rather long, b) I didn't know it was a view at first, c) the actual view contains information I'd rather was kept private, so I'd have to massage it anyway, and d) it worked fine in Sybase.

I don't expect anyone to debug my code for me, so I shorten it down to what I think will isolate the problem. The "jist" of it. Failing to get resolution at that point, the actual code is out there, should anyone think that'll help. But I want to save the =hard= work for later, in case it isn't needed.

> The manual says that 1791 indicates an error in the ORDER BY
> clause of a SELECT DISTINCT query. Either Oracle is broken or
> your actual failing query had an ORDER BY clause.

This is something to investigate. The view does not contain an ORDER BY, but the SELECT DISTINCT on the view does. At least it does in some parts of the app, while not in others.

Playing around with it, I discovered I can change the query to:

select distinct alias || ' ' || name "newname" from theview
order by newname

and this works without an error. So I might drop the new view I created.

> There is a perfectly sensible restriction on ORDER BY in
> SELECT DISTINCT queries.

Perhaps so, but it wasn't a restriction that Sybase considered sensible. In making the conversion from Sybase to Oracle, I naturally expected some incompatibilities, but on the surface, this one looked rather strange.

> To quote the manual again:
>
> If the ORDER BY clause and the DISTINCT operator both appear
> in a SELECT statement, the ORDER BY clause cannot refer
> to columns which do not appear in the select list.
>
> Useful things - these manuals!

Sure, once you have all 50,000 pages memorized and mentally cross-indexed, they're wonderful! :-)

This actually happens, of course, and the phenomenon is called "experience." But I ain't got none yet.

Most of the time, when you get an error like "NNNN - you can't do that", and you look up the error number in the manual, you are cheerfully informed that "NNNN indicates that you tried to do something you can't do. Don't try to do it anymore."

Whoa, that's helpful!!

Cheers! And thanks for the tips!

A. Received on Thu Jan 09 1997 - 00:00:00 CST