This content has been marked as final. Show 16 replies
What version of the database are you using? If you are on 11g and up I recommend using "Virtual Columns." If you are on a previous version of the database I recommend possibly placing this code in a database view. In most cases I don't like adding calculated columns to tables because it's another element that must be kept in sync and that presents problems. I think "Virtual Columns" is a nice solution for the cases where you want this capability.
Richard wrote:I would Second Centinul on opinion to prevent the addition of Field 3 in your scenario.
I am wanting to create a field that checks another field in the same table. If the field checked is a specific value I would like for the "boolean" field to be shown as Y for "yes".
FIELD 1 | FIELD 2 | FIELD 3
Friend Random Y
Enemy Random N
So the idea is that field three will automatically populate with "Y" when Friend is entered or "N" when enemy is entered. I am attempting to do this in Oracle.
Any help would be appreciated, someone told me to look into ISLEAF, but I am clueless on that. Tried searching didn't find much.
Thanks in Advance!
Reason is, it destroys/contradicts the 3rd Normal form of your database. Read 3rd Normal Form example for better understanding.
Since, this is a Derived Field, it can always be fetched depending on the values of Field1 column. Hence, I, in your situation, would not add the field.
However, If you have to add it anyway, you can use Centinul's suggestion of Virtual Column. If you are not on Oracle 11g yet, I would suggest you to use a Before Insert/Update trigger:
Untested Rough Code:-
create or replace trigger upd_field_3 before insert or update of Field_1 on table_name for each row begin if :new.field_1 := 'FRIEND' then :new.field_3 := 'Y'; elsif :new.field_1 := 'ENEMY' then :new.field_3 := 'N'; end if; end upd_field_3;
right, but let's say I have this:
CREATE TABLE MASTER.NAME_TABLE
NAME VARCHAR2(10 BYTE),
PARENT VARCHAR2(32 BYTE),
DESC VARCHAR2(80 BYTE),
ISLEAF VARCHAR2(1 BYTE)
I Am going to bring values into this first three fields: NAME,PARENT, and DESC. The idea is to create a "process" that would run on the 4th field "ISLEAF" this would look at PARENT and update with 'Y' or 'N' depending on the given value.
Since you are on 11g as everyone suggested using a virtual column will help..
SQL> SQL> CREATE TABLE NAME_TABLE 2 ( 3 NAME_1 VARCHAR2(1 ), 4 PARENT_1 VARCHAR2(6 ), 5 DESC_1 VARCHAR2(1 ), 6 ISLEAF_1 VARCHAR2(1) GENERATED ALWAYS as ((decode(PARENT_1,'FRIEND','Y','ENEMY','N',null) )) virtual 7 ); Table created SQL> insert into name_table(NAME_1,parent_1,desc_1) values('A','FRIEND','B'); 1 row inserted SQL> insert into name_table (NAME_1,parent_1,desc_1) values('A','ENEMY','B'); 1 row inserted SQL> commit; Commit complete SQL> select * from name_table; NAME_1 PARENT_1 DESC_1 ISLEAF_1 ------ -------- ------ -------- A FRIEND B Y A ENEMY B N SQL>
I am not seeing what I am doing wrong I think this should be fairly easy. This does what it is supposed to, is adds a Y for the value I need and N for anything else. But I cannot include it as a column
create table ISLEAF_table as
case when TEST_TABLE.object_name = 'HAPPY' then 'Y' ELSE 'N' END AS ISLEAF FROM TEST_TABLE
The table I am trying to include it in the SELECT clause so I can reference this in another table. How can I do this?