CODEWITHSUNDEEP
sqloracle-database
subi_speedrunner
subi_speedrunner

Reputation: 921

Issue while querying a alias column in oracle

I am trying to solve this using ORACLE query. Following is the sample data

|     Col1       |      Col2    |
------------------------------------
| 21-dec-15      | nochange     |
| 20-dec-15      | change       |
| 20-dec-15      | nochange     | 
| 18-dec-15      | change       |
| 18-dec-15      | nochange     |

Here col2 is a alias column, not a column from table.

Here the requirement is for a particular date I need to check whether any change is happening, If change is there then update nochange to change for that date.

As col2 is a alias column so I am not sure how to check it. I am also ok if we are storing the result in a separate alias column.

Expected Result:

|     Col1       |      Col2  |
------------------------------------
| 21-dec-15      | nochange     |
| 20-dec-15      | change       |
| 20-dec-15      | change       | 
| 18-dec-15      | change       |
| 18-dec-15      | change       |

Upvotes: 0

Views: 47

Answers (1)

Vampiro
Vampiro

Reputation: 335

This should do it:

with tempt as 
(
   <<Your existing query that returns col1 and col2>>
)
select t1.col1,
       case when exists ( select 1 from tempt t2 where t1.col1=t2.col1 and t2.col2='change')
            then 'change'
            else t1.col2
       end
  from tempt t1;

Add an order by col1 if needed.

Upvotes: 2

Related Questions