Re: select distinct on computed column

Alvin Sylvain wrote:
>
> 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.
>
> Is there a cheap easy way around this?
>
> Thanks in advance for any pointers.

What do you mean by "Oracle does not allow this" ? Do you get an ORA-error (which you should not) or you don't get only distinct values?

Oracle definitevely works with the above statement so if you get error message then it must be your typo in the sintax.

If you get more rows than expected and some of them appear to be identical, then the reason might be that one or both of your columns are of type CHAR (instead of VARCHAR2) and the values in them are padded with different number of blanks. The other possibility is, that two or more rows in the table have same values in one column and NULL values in other column. Note that condition NULL = NULL allways returns FALSE.

Jure

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================