1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to set conditional default values in tables?..

Discussion in 'SQL PL/SQL' started by Vicky, May 13, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi..,

    I'm trying set conditional default values for a column.. And I got Error while using Decode to achieve that...


    Code (SQL):


    ALTER TABLE EMPLOYEES ADD
    ( elig_chk varchar2(1) DEFAULT (decode(JOB_ID,'CLERK','Y','N')) );


    Error report:
    SQL Error: ORA-00984: COLUMN NOT allowed here
    00984. 00000 -  "column not allowed here"
    *Cause:    
    *Action:

     

    Cud U tel me, how can I achieve dis?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#SQLRF01402

    For example:

    Code (SQL):


    DROP TABLE test_dcd;

    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) AS (decode(MOD(id,2),0,'even','not even'))
    );  

    INSERT INTO test_dcd(id) SELECT level FROM dual CONNECT BY level < 11;
    commit;

    SELECT * FROM test_dcd;


     
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Sergey,

    I'm unable to create the table...


    Code (SQL):


    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) AS (decode(MOD(id,2),0,'even','not even'))
    );


    Error report:
    SQL Error: ORA-00907: missing RIGHT parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:


     


    I'm getting this error.. And, the Oracle Version, which I'm using is..


    Code (SQL):



    SELECT * FROM V$VERSION;


    BANNER                                                        
    ----------------------------------------------------------------
    Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production                          
    CORE    10.2.0.1.0  Production                                        
    TNS FOR Linux: Version 10.2.0.1.0 - Production                  
    NLSRTL Version 10.2.0.1.0 - Production


     
    Cud U tel me what's the prblm here..?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need another ) before the semicolon.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Vicky.

    if the version lower than 11, then it is possible to use the trigger or to process in procedure...

    for eaxmple:

    Code (SQL):


    DROP TABLE test_dcd;
     
    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) NOT NULL
    );  

    CREATE OR REPLACE TRIGGER tbi_test_dcd
        BEFORE INSERT
        ON test_dcd
        FOR each ROW
    BEGIN
        :NEW.str := CASE MOD(:NEW.id,2) WHEN 0 THEN  'even' ELSE 'not even' END;
    END;    

    /
     
    INSERT INTO test_dcd(id) SELECT level FROM dual CONNECT BY level < 11;
    commit;
     
    SELECT * FROM test_dcd;

     

    Code (SQL):


    DROP TABLE test_dcd;
     
    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) NOT NULL
    );  


    DECLARE

        PROCEDURE ins_test_dcd
            (
                p_id   INT,
                p_str  varchar2 DEFAULT NULL
            )
        AS
        BEGIN
            INSERT INTO test_dcd
            VALUES (p_id,COALESCE(p_str,CASE MOD(p_id,2) WHEN 0 THEN  'even' ELSE 'not even' END));
        END;        

    BEGIN
        FOR z IN 1 .. 10
        loop
            ins_test_dcd(z,NULL);
        END loop;    
       
        commit;
    END;        
    /
    SELECT * FROM test_dcd;

     
     
    Vicky likes this.
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Sergey pasted the relevant portion of the Oracle documentation for the major portion of your problem. You cannot have a DEFAULT that references another column in the table. Since your DECODE references the ID column -- it won't work regardless of any parenthesis issues being resolved. A trigger is the best bet for populating that value.
     
    Vicky likes this.
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    As U said., For 11G, I tried Using Virtual columns.,

    I used the following 2 create commands...


    Code (SQL):


    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) AS (decode(MOD(id,2),0,'even','not even'))
    );  

     
    &

    Code (SQL):


    CREATE TABLE test_dcd
    ( id INT PRIMARY KEY,
      str varchar2(8) generated always AS (decode(MOD(id,2),0,'even','not even')) virtual
    );  

     

    For the abv 2 commands its creating successfully., Then, Cud U tel me wat's the differenz between them.,?! Isn't those keywords necessary..!
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    These are optional members of syntax.....

     
    Vicky likes this.
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnk UU very much for the Clear explanation Sergey...:)
     
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Sergey,

    I'm facing an issue on using Virtual columns.. When I'm trying to insert records from old to new table, I'm getting the below error..


    Code (SQL):


    Error report:
    SQL Error: ORA-54013: INSERT operation disallowed ON virtual COLUMNS

     

    If U're suggesting me to mention column names in 'Insert' statements.. SInce, I've to do that in many places., It'll be a tedious task for me.. Is der any alternative to overcome dis prblm./!
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi, Vicky.

    yes,is necessary to specify columns (all mandatory), excepting the virtual column.

    the alternative is to write the trigger
     
    Vicky likes this.
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot include the virtual columns in the insert list; those are calculated values based on other columns in the table. You can only insert into the REAL columns using an insert statement. As an example:


    The EMP table is created with a virtual column named TTL_COMP and is calculated from the sal and comm values in each row. Inserting a record into that EMP table looks like this --


    SQL> insert into emp
    2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    3 values
    4 (0002, 'FNARM', 'IRRITANT', 7369, add_months(sysdate, -120), 600, 300, 30);


    1 row created.


    SQL>


    The TTL_COMP virtual is computed after the record is inserted. Notice that the TTL_COMP column is NOT listed in the columns being inserted into.
     
    Vicky likes this.