CODEWITHSUNDEEP
sqloracle11g
Antoine
Antoine

Reputation: 315

Using part of the select clause without rewriting it

I am using an Oracle SQL Db and I am trying to count the number of terms starting with X letter in a dictionnary.

Here is my query :

SELECT Substr(Lower(Dict.Term),0,1) AS Initialchar,
       Count(Lower(Dict.Term))
FROM Dict
GROUP BY Substr(Lower(Dict.Term),0,1)
ORDER BY Substr(Lower(Dict.Term),0,1);

This query is working as expected, but the thing that I'm not really happy about is the fact that I have to rewrite the long "Substr(Lower(Dict.Term),0,1)" in the GROUP BY and ORDER BY clause. Is there any way to reuse the one I defined in the SELECT part ?

Thanks

Upvotes: 1

Views: 52

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21170

I prefer subquery factoring for this purpose.

with init as (
 select  substr(lower(d.term), 1, 1) as Initialchar
 from dict d)
select Initialchar, count(*)
from init
group by Initialchar
order by Initialchar;

Contrary to opposite meaning, IMO this makes the query much clearer and defines natural order; especially while using more subqueries.

I'm not aware about performance caveats, but there are some limitation, such as it not possible to use with clause within another with clause: ORA-32034: unsupported use of WITH clause.

Upvotes: 0

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31203

Not directly. The output columns can only be referred to in the ORDER BY clause, but not used in any other way. The only way would be to make it into a subselect, but it wouldn't be any clearer and might cause issues with performance.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can use a subquery. Because Oracle follows the SQL standard, substr() starts counting at 1. Although Oracle does explicitly allow 0 ("If position is 0, then it is treated as 1"), I find it misleading because "0" and "1" refer to the same position.

So:

select first_letter, count(*)
from (select d.*, substr(lower(d.term), 1, 1) as first_letter
      from dict d
     ) d
group by first_letter
order by first_letter;

Upvotes: 2

Related Questions