CODEWITHSUNDEEP
sqloracle-databasecasealias
user966588
user966588

Reputation:

Use same value for multiple columns in select part of sql

I am using 

case 
when condition1 then ....
when condition2 then ....
when condition3 then ....
end aliasname1

Now I want to use same value for aliasname2. I read that we can't use multiple aliases for same column.

Also I don't want to use subquery in from clause.

So If case returns value, suppose 10, I want same value to be assigned to aliasname1 and aliasname2.

How can I do this?

Update: Currently I am using it like this

case 
when condition1 then ....
when condition2 then ....
when condition3 then ....
end aliasname1
case 
when condition1 then ....
when condition2 then ....
when condition3 then ....
end aliasname2

That is using same case(copy paste) for aliasname2. Which doesn't really look good to my eyes because of repetition.

Upvotes: 0

Views: 832

Answers (2)

alzaimar
alzaimar

Reputation: 4622

Either copy your case construct

select case 
when condition1 then ....
when condition2 then ....
when condition3 then ....
end aliasname1,
case 
when condition1 then ....
when condition2 then ....
when condition3 then ....
end aliasname2
...

or

select aliasname1, aliasname1 as aliasname2
from (
  select
    case 
    when condition1 then ....
    when condition2 then ....
    when condition3 then ....
    end aliasname1
    ...
) x

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

If I'm understanding your question, you can use the same column with different aliases.

This works just fine:

SELECT Field as Alias1, Field as Alias2 
FROM TABLE

Here is the SQL Fiddle to show you.

To answer your question,

SELECT 
   CASE WHEN <SOME CRITERIA> THEN 10 END as Alias1,
   CASE WHEN <SOME CRITERIA> THEN 10 END as Alias2
...

Good luck.

Upvotes: 1

Related Questions