Home » SQL & PL/SQL » SQL & PL/SQL » Conditional based default value (Oracle 11 )
Conditional based default value [message #657348] Mon, 07 November 2016 04:34 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All,

I want to add the column with conditional based default value to a table .
Does Oracle supports this functionality ?

Example :

drop table  default_test;
create table  default_test as
select  'one'  name , 1 no  from  dual connect by level  < 4 union select  'two' , 2  from  dual connect by level  < 4 ;

alter table  default_test  add  new_col  varchar2(20)  default decode (name,'one','new_one', 'two','new_two','default_value')


Last statement was failing .
Is there any other way to do the same ?

Thanks
SaiPradyumn
Re: Conditional based default value [message #657349 is a reply to message #657348] Mon, 07 November 2016 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you ever want the column to have a value other than what the decode specifies?
If not - make it a virtual column instead.
Otherwise you can use a trigger.
Re: Conditional based default value [message #657351 is a reply to message #657349] Mon, 07 November 2016 06:37 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks cookiemonster .

If it only the DECODE values is there any other solution ?
Re: Conditional based default value [message #657355 is a reply to message #657351] Mon, 07 November 2016 07:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Use virtual column, as cookiemonster already suggested.

SY.
Re: Conditional based default value [message #657356 is a reply to message #657351] Mon, 07 November 2016 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#BABCHBHE

Re: Conditional based default value [message #657357 is a reply to message #657355] Mon, 07 November 2016 08:05 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Thank you vey much. Its working fine .
Previous Topic: package modified details
Next Topic: conversion to 16bit decimal
Goto Forum:
  


Current Time: Thu Mar 28 19:15:34 CDT 2024