CODEWITHSUNDEEP
sqldatabaseoracle-database
Carven
Carven

Reputation: 15638

Is it possible to assign select values to variables in sql?

How can I assign a selected value to a variable so that I can reuse the value in a nested sql? For example, I may have something that looks like this: 

select count(user_id), substr(name, 0, 5)
from users
group by substr(name, 5);

But in this case, the substr value in the select clause may be inconsistent with the substr value in the group by clause. It will be good if I can assign one of the substr value to a variable and reuse it. This will ensure that the values are consistent. How can I achieve this in Oracle?

Upvotes: 0

Views: 336

Answers (3)

logan
logan

Reputation: 8346

Try this with alias; Other operations such as calling in sub query is costly operation

In oracle,

select count(a.user_id), a.name from (
    select user_id, substr(name, 0, 5) name
    from users) a
    group by a.name;

In Many other Major Databases, try with alias which is faster !

select count(user_id), substr(name, 0, 5) name
from users
group by name;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

To avoid the duplication of terms in the select list and group by clause, you could use a subquery:

select count(user_id), name_alias
from (
  select user_id, substr(name, 0, 5) as name_alias
  from users
)
group by name_alias;

It seems like overkill in this simple example, but it can be useful with complicated case statements etc. where it saves some work keeping the code consistent; though Oracle would complain if you made a mistake.

You need the subquery because Oracle doesn't allow you to use column aliases in the same level of query, except in the order by, because of how the query is constructed internally.

Upvotes: 1

eric.itzhak
eric.itzhak

Reputation: 16062

If you wish to assign variables, you need to switch to PL/SQL, since you're saying you try to avoid that, then there's no variables in regular querying,unless you wish to involve server side code.

if you wish to deal with the result as a column instead you could do something like:

select count(t.user_id),t.name
from(
     select user_id,substr(name,0,5) name
     from users
) t
group by t.name

Upvotes: 2

Related Questions