Home » SQL & PL/SQL » SQL & PL/SQL » Table Creation Problem (Oracle, 11G, Unix)
Table Creation Problem [message #576969] Mon, 11 February 2013 07:49 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi

I am trying to create table with Default Values which will be decided for each row based on CASE Statement. But the query is giving error saying "Column not allowed here"

CREATE TABLE TABLE_NEW
(
  INSTALLATION_ID          VARCHAR2(50 BYTE)        NULL,
  TRANSACTION_ID           VARCHAR2(50 BYTE)        NULL,
  SERVER_ID                VARCHAR2(50 BYTE)        NULL,
  CLINICAL_TRANSACTION_ID  VARCHAR2(255 BYTE)       NULL,
  RESPONSE_TIME            NUMBER(10,3)             NULL,
  TRANSACTION_START_TIME   TIMESTAMP(3)             NULL,
  TRANSACTION_END_TIME     TIMESTAMP(3)             NULL,
  LOCATION_ID              VARCHAR2(50 BYTE)        NULL,
  WAIT_TIME                NUMBER(10,3)             NULL,
  INTERNAL_TRANSACTION_ID  VARCHAR2(50 BYTE)        NULL,
  INTERNAL_TIME            NUMBER(10,3)             NULL,
  EXTERNAL_SERVICE_ID      VARCHAR2(50 BYTE)        NULL,
  EXTERNAL_SERVICE_TIME    NUMBER(10,3)             NULL,
  LOCAL_SERVICE_ID         VARCHAR2(50 BYTE)        NULL,
  LOCAL_SERVICE_TIME       NUMBER(10,3)             NULL,
  MESSAGE_GUID             VARCHAR2(256 BYTE)       NULL,
  RETURN_MESSAGE_GUID      VARCHAR2(256 BYTE)       NULL,
  FILE_NAME                VARCHAR2(100 BYTE)       NULL,
  DATE_LOADED              DATE                     NULL,
  TRS_SIZE                 NUMBER(14,3)             NULL,
  USER_DETAIL_FLAG         VARCHAR2(1 BYTE)     DEFAULT CASE  WHEN ("WAIT_TIME" IS NULL AND "INTERNAL_TRANSACTION_ID" IS NULL AND "INTERNAL_TIME" IS NULL AND "EXTERNAL_SERVICE_ID" IS NULL AND "EXTERNAL_SERVICE_TIME" IS NULL AND "LOCAL_SERVICE_ID" IS NULL AND "LOCAL_SERVICE_TIME" IS NULL AND "MESSAGE_GUID" IS NULL AND "RETURN_MESSAGE_GUID" IS NULL AND "INSTALLATION_ID" IS NOT NULL AND "CLINICAL_TRANSACTION_ID" IS NOT NULL) THEN 'U' ELSE 'D' END     NULL
)
PARTITION BY RANGE (DATE_LOADED) 
(  
  PARTITION SRC_13102012 VALUES LESS THAN (TO_DATE(' 2012-10-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_14102012 VALUES LESS THAN (TO_DATE(' 2012-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_15102012 VALUES LESS THAN (TO_DATE(' 2012-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_16102012 VALUES LESS THAN (TO_DATE(' 2012-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_17102012 VALUES LESS THAN (TO_DATE(' 2012-10-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_18102012 VALUES LESS THAN (TO_DATE(' 2012-10-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_19102012 VALUES LESS THAN (TO_DATE(' 2012-10-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_20102012 VALUES LESS THAN (TO_DATE(' 2012-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_21102012 VALUES LESS THAN (TO_DATE(' 2012-10-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_22102012 VALUES LESS THAN (TO_DATE(' 2012-10-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_23102012 VALUES LESS THAN (TO_DATE(' 2012-10-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_24102012 VALUES LESS THAN (TO_DATE(' 2012-10-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_25102012 VALUES LESS THAN (TO_DATE(' 2012-10-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_26102012 VALUES LESS THAN (TO_DATE(' 2012-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_27102012 VALUES LESS THAN (TO_DATE(' 2012-10-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_28102012 VALUES LESS THAN (TO_DATE(' 2012-10-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_29102012 VALUES LESS THAN (TO_DATE(' 2012-10-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_30102012 VALUES LESS THAN (TO_DATE(' 2012-10-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_31102012 VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    NOCOMPRESS,  
  PARTITION SRC_31123000 VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    NOCOMPRESS
)
NOCOMPRESS 
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
MONITORING
ENABLE ROW MOVEMENT;
Re: Table Creation Problem [message #576970 is a reply to message #576969] Mon, 11 February 2013 08:18 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
problem in default value of USER_DETAIL_FLAG column , a column can have simple a value or
expression not any conditions like-case,if..else etc.

thanks.....

[Updated on: Mon, 11 February 2013 08:41]

Report message to a moderator

Re: Table Creation Problem [message #576973 is a reply to message #576969] Mon, 11 February 2013 08:37 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
You cannot refer to a column in a DEFAULT clause. This is documented.
Re: Table Creation Problem [message #576975 is a reply to message #576973] Mon, 11 February 2013 08:40 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Yes, Problem is with Default column,

This code has been taken from existing system, and it worked fined earlier(which has been developed by someone else).

Now i want to create the same in different system. then it is given erro.

Thanks
Re: Table Creation Problem [message #576976 is a reply to message #576970] Mon, 11 February 2013 08:43 Go to previous messageGo to next message
gaurav_katyal05
Messages: 4
Registered: February 2013
Location: India
Junior Member
Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

Instead of that you have the option to create a trigger
CREATE OR replace TRIGGER t1
  BEFORE INSERT ON table_new
  FOR EACH ROW
BEGIN
    IF :NEW.user_detail_flag IS NULL THEN
      IF ( :NEW.wait_time IS NULL
           AND :NEW.internal_transaction_id IS NULL
           AND :NEW.internal_time IS NULL
           AND :NEW.external_service_id IS NULL
           AND :NEW.external_service_time IS NULL
           AND :NEW.local_service_id IS NULL
           AND :NEW.local_service_time IS NULL
           AND :NEW.message_guid IS NULL
           AND :NEW.return_message_guid IS NULL
           AND :NEW.installation_id IS NOT NULL
           AND :NEW.clinical_transaction_id IS NOT NULL ) THEN
        :NEW.user_detail_flag := 'U';
      ELSE
        :NEW.user_detail_flag := 'D';
      END IF;
    END IF;
END; 

[Updated on: Mon, 11 February 2013 08:50] by Moderator

Report message to a moderator

Re: Table Creation Problem [message #576977 is a reply to message #576976] Mon, 11 February 2013 08:51 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum, Gaurav. It is really nice to see a new member whose first posting is trying to help. All too often, people only ask, and never contribute (or say "thank you").

Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
I've formatted your code using this formatter http://www.dpriver.com/pp/sqlformat.htm and enclosed it in tags, do you see how much easier it is to read?
Re: Table Creation Problem [message #576982 is a reply to message #576977] Mon, 11 February 2013 09:48 Go to previous messageGo to next message
gaurav_katyal05
Messages: 4
Registered: February 2013
Location: India
Junior Member
Hi John

In future, I, will definitely follow your suggestion.
And, also I have now gone through the OraFAQ Forum Guide .
Re: Table Creation Problem [message #576987 is a reply to message #576976] Mon, 11 February 2013 10:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
gaurav_katyal05 wrote on Mon, 11 February 2013 09:43
Instead of that you have the option to create a trigger


No, you don't. It is impossible in a trigger to determine if user supplied NULL as column USER_DETAIL_FLAG value or column was omitted in INSERT statement and NULL was assigned by Oracle.

SY.

[Updated on: Mon, 11 February 2013 10:24]

Report message to a moderator

Re: Table Creation Problem [message #576992 is a reply to message #576987] Mon, 11 February 2013 11:24 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
Are you sure, Solomon? I think I'm getting the desired result, whether I omit the value or specify it as null:
orcl>
orcl> create table t1(c1 number,c2 number);

Table created.

orcl>
orcl> create or replace trigger trig1 before insert on t1 for each row
  2  begin
  3  if :new.c1 is null then :new.c2 := 0;
  4  else :new.c2 := 1;
  5  end if;
  6  end;
  7  /

Trigger created.

orcl> insert into t1 values(null,null);

1 row created.

orcl> insert into t1 values(1,null);

1 row created.

orcl> insert into t1(c2) values(null);

1 row created.

orcl> select * from t1;

        C1         C2
---------- ----------
                    0
         1          1
                    0

orcl> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

orcl>
(since OP appears to have lost interest, this is of course purely for my own educational purposes)
Re: Table Creation Problem [message #576993 is a reply to message #576992] Mon, 11 February 2013 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what Solomon said, you cannot distinguish if you set c1 to NULL or if you did not set c1.

Regards
Michel
Re: Table Creation Problem [message #577005 is a reply to message #576987] Mon, 11 February 2013 13:02 Go to previous message
gaurav_katyal05
Messages: 4
Registered: February 2013
Location: India
Junior Member
Hi Solomon

I got your point. The trigger ,I, proposed is not the solution.

Thanks! for correcting my solution.
Previous Topic: Pivot output in equijoin
Next Topic: Copy data from one table to another- Oracle
Goto Forum:
  


Current Time: Tue Sep 23 21:26:07 CDT 2014

Total time taken to generate the page: 0.12662 seconds