CODEWITHSUNDEEP
sqloracle-databasedatecasesql-view
doremi
doremi

Reputation: 161

How to replace date value in SQL?

I am using Oracle and trying to create a view in which I will replace one date value with another date value. I am trying to use case when statement, however, I need to use the same column name as an alias and I get the error that I have duplicate column names. Do you know how to fix it or propose the alternative for replacing date value in a column and creating view? I need to keep the original table unchanged. Code:

create view name_of_view as
select t.*,
(case when birth_date = to_date('4.12.2015', 'DD.MM.YYYY')
 then to_date('4.12.1950', 'DD.MM.YYYY')
 else birth_date end) as birth_date
 from table t;

Upvotes: 0

Views: 1626

Answers (1)

Ergi Nushi
Ergi Nushi

Reputation: 863

As @Lukasz Szozda has suggested in the comments, when you try t.* it will retrieve all columns from your table, including birth_date. So when you add another birth_date as part of your case when, you receive the duplicate column name error.

What you need to do is:

  • You either change the case when column name to something like: birth_date_new or whatever then you will have both of the columns.
  • You retrieve all columns by their names and when retrieving birth_date you apply case when.

Upvotes: 1

Related Questions