CODEWITHSUNDEEP
oracle-databaseselect
Dinesh Manne
Dinesh Manne

Reputation: 1932

Oracle: Using Pseudo column value in the same Select statement

I have a scenario in oracle where i need to be able to reuse the value of a pseudo column which was calculated previously within the same select statement something like:

select 'output1' process, process || '-Output2' from Table1

I don't want to the repeat the first columns logic again in the second column for maintenance purposes, currently it is done as

select 'output1' process, 'output1' || '-Output2' name from Table1

since i have 4 such columns which depend on the previous column output, repeating would be a maintenance nightmare

Edit: i included table name and removed dual, so that no assumptions are made about that this is not a complex process, my actual statement does have 2 to 3 joins on different tables

Upvotes: 5

Views: 5235

Answers (3)

Jay
Jay

Reputation: 5043

very similar to the previous poster, but I prefer the method I show below, it is more readable, thusly more supportable, in my opinion

select val1 || val2 || val3 as val4, val1 from (
   select 'output1' as val1, '-output2' as val2, '-output3' as val3 from dual
)

Upvotes: 2

Chi
Chi

Reputation: 23164

You can also use the subquery factoring syntax - i think it is more readable myself:

with tmp as
(  
  select 'output' process from dual
)
select 
  process, process || '-Output2' from tmp;

Upvotes: 9

Adam Paynter
Adam Paynter

Reputation: 46908

You could calculate the value in a sub-query:

select calculated_output process, calculated_output || '-Output2' name from
(
    select 'output1' calculated_output from dual
)

Upvotes: 10

Related Questions