Hi Tom,
I have three tables Employee, Employee Details and Decode.
In first table Employee, I have three columns having 5 records:
create table Employee ( Emp_No int, Emp_Type varchar2(5), Emp_Name varchar2(30) );
with this data in it:
insert into Employee values ( 1, 'S', 'Employee 1');
insert into Employee values ( 2, 'S', 'Employee 2');
insert into Employee values ( 3, 'S', 'Employee 3');
insert into Employee values ( 4, 'S', 'Employee 4');
insert into Employee values ( 5, 'S', 'Employee 5');
In second table Employee_Details
create table Employee_Details( Emp_No int, Emp_Type varchar2(5));
Now I am adding third column in table Employee_Details with following command:
Alter table Employee_Details
Add (Emp_Code varchar2(15) default 'SPA' not null);
Records in second table:
insert into Employee values ( 4, 'S', 'ENG');
insert into Employee values ( 5, 'S', 'SPA');
In third table Decode,
create table Decode( Code varchar2(5), Value varchar2(20), Category varchar2(50));
with values:
insert into Employee values ( ENG, 'English', 'TYPE_CODE');
insert into Employee values ( SPA, 'Spanish', 'TYPE_CODE');
Now when I run following query:
Select e.Emp_No, e.emp_Type,
(ed.Emp_Code || '-' ||
(Select d.value from Decode d Where d.Category='TYPE_CODE' and d.code=ed.Emp_Code)) as Code_Type
From Employee e
Left Outer Join Employee_Details ed
On e.Emp_No = ed.Emp_No
and e.Emp_Type = ed.Emp_Type;
It is giving me this result:
Emp_No Emp_Type Code_Type
------ -------- --------
1 S -Spanish
2 S -Spanish
3 S -Spanish
4 S ENG-English
5 S SPA-Spanish
Want to know why it is giving value in Code_Type for first 3 records. Ideally they should be coming as "-" only.
Please advise.
Thanks for providing a test case!
There are some bugs related to adding not null columns with a default. e.g. see MOS note 1106553.1. One way to solve this to split setting the default and not null into separate steps.
create table Employee ( Emp_No int, Emp_Type varchar2(5), Emp_Name varchar2(30) );
insert into Employee values ( 1, 'S', 'Employee 1');
insert into Employee values ( 2, 'S', 'Employee 2');
insert into Employee values ( 3, 'S', 'Employee 3');
insert into Employee values ( 4, 'S', 'Employee 4');
insert into Employee values ( 5, 'S', 'Employee 5');
commit;
create table Employee_Details( Emp_No int, Emp_Type varchar2(5));
Alter table Employee_Details
Add (Emp_Code varchar2(15) default 'SPA');
Alter table Employee_Details
modify (Emp_Code not null);
insert into Employee_Details values ( 4, 'S', 'ENG');
insert into Employee_Details values ( 5, 'S', 'SPA');
create table Decode( Code varchar2(5), Value varchar2(20), Category varchar2(50));
insert into Decode values ( 'ENG', 'English', 'TYPE_CODE');
insert into Decode values ( 'SPA', 'Spanish', 'TYPE_CODE');
commit;
Select e.Emp_No, e.emp_Type,
(ed.Emp_Code || '-' ||
(Select d.value from Decode d Where d.Category='TYPE_CODE' and
d.code=ed.Emp_Code)) as Code_Type
From Employee e
Left Outer Join Employee_Details ed
On e.Emp_No = ed.Emp_No
and e.Emp_Type = ed.Emp_Type;
EMP_NO EMP_T CODE_TYPE
---------- ----- ------------------------------------
4 S ENG-English
5 S SPA-Spanish
2 S -
3 S -
1 S -
The issue is fixed in 12c.