0

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;
doremi
  • 141
  • 3
  • 15
  • 1
    t.* is an antipattern. Use full column list. Oracle does not support [SELECT * EXCEPT/REPLACE syntax](https://stackoverflow.com/questions/14031225/rename-single-column-in-select-in-sql-select-all-but-a-column/52243902#52243902) – Lukasz Szozda Dec 03 '19 at 17:02
  • And is there another option in which using the full column list is not needed? My table has around 100 columns. – doremi Dec 03 '19 at 17:07
  • I don't insist on using case statement for replacing, I just need to create the view and do not change existing table. – doremi Dec 03 '19 at 17:09
  • `SELECT col1, col2, ...` You don't need to write it by hand. Just drag and drop from object explorer(if you are using SQL Developer) – Lukasz Szozda Dec 03 '19 at 17:09

1 Answers1

1

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.
Ergi Nushi
  • 837
  • 1
  • 6
  • 17
  • I need to keep the same column name: `birth_date` for the view, just change the value in it. So there is no other replace option than `case when` that woul allow me to replace it for the view? – doremi Dec 03 '19 at 17:17
  • For example, update the view somehow in the same fashion as update table works? – doremi Dec 03 '19 at 17:22
  • Without listing all the columns - I want to avoid a superlong query – doremi Dec 03 '19 at 17:23
  • In fact no, you either select (*) or you select specifics. https://stackoverflow.com/a/9133172/8943967 Or if it is a must, you can write a pl/sql block and do lots of queries to achieve what you want. – Ergi Nushi Dec 03 '19 at 17:32
  • @doremi use as "Birth_Date" then to alias the case statement by keeping asterisk. – Barbaros Özhan Dec 03 '19 at 17:33