Mix DISTINCT and UPPER keywords together

Question

I have this query on Oracle 10: ``` SELECT DISTINCT NOME FROM ICT.UTENTE WHERE UPPER(nome) LIKE UPPER('MA%'); ``` This works and get me something like: ``` MARIA LUISA Mariano MARIO ``` What I really would is to get each row in upper case, but I can't figure out a way to mix `DISTINCT` and `UPPER` keywords together. I have tried to replace first query line with any of this: ``` SELECT DISTINCT UPPER(nome) -- not a SELECTed expression SELECT UPPER (DISTINCT nome) -- missing expression SELECT DISTINCT UPPER nome -- upper: invalid identifier SELECT UPPER DISTINCT nome -- FROM keyword not found where expected ``` but I always got troubles! Is subquerying the only solution?

Accepted Answer

This should work.

```
SELECT DISTINCT UPPER(nome)   
```

Indeed it does work. If you're getting this error ...

```
ORA-01791: not a SELECTed expression
```

...then you haven't posted the whole query. Specifically you're not showing us the ORDER BY clause. With a DISTINCT the attributes in the ORDER BY clause must match the projection. So either you need to ...

```
ORDER BY upper(nome)
```

... or you can cheat and sort by position instead ...

```
ORDER BY 1
```

Mix DISTINCT and UPPER keywords together

Solution

Problem