Home » SQL & PL/SQL » SQL & PL/SQL » calculated column in 10g r2 (oracle 10 g r2)
calculated column in 10g r2 [message #570981] Mon, 19 November 2012 08:27 Go to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Hi ,

In my table there are 188 column , i compare these with other table to know which column values have changed. i use ora_hash to get hash value and then i compare with other table.

I want to create calcualated column but i am unable to create as it says invalid data type.

alter table STG_TBLDISRUPTIONS add stg_hash as  ORA_HASH (INTINTERNALID  ||  VCHIMSNO  ||  CHRFANDDNO  ||  INTINCIDENTNO  ||  VCHDESCRIPTIONFAULT 
 ||  TININCIDENTTYPEID  ||  TINLINEID  ||  DTMINCIDENTFAULTSTARTDATETIME  ||  DTMNOTIFICATIONDATETIME 
 || DTMSTARTDATETIME  ||  DTMENDDATETIME  ||  DTMACTUALENDDATETIME  ||  DTMDURATIONENDDATETIME 
 ||   CHRFROMBRSSTATIONID  ||  CHRTOBRSSTATIONID  ||  VCHLOCATIONDESC  ||  CHRDIRECTIONID  ||  SINEXTRARUNNINGTIME 
 ||   CHRPLATFORMNO  ||  BLNMULTIPLESIGNAL  ||  TINNOOFPEOPLETRAPPED  ||  CHRMODEOFFAILURE  ||  TINDAYOFWEEKTABLEID 
 ||  TINTYPEOFSTATIONCLOSUREID  ||  BLNHEADWAY  ||  BLNPRIMARY  ||  BLNBOOKEDACCESS  ||  BLNNODEFECTFOUND 
 ||  BLNREASONABLENOTIFICATION  ||  DECNACHSVALUE  ||  BLNUSEDINPAYMENT  ||  DTMUSEDINPAYMENT  ||  TINCLOSEDPERIOD 
 ||  INTCLOSEDYEAR  ||  DECPAYMENTNACHSVALUE  ||  DECPREVIOUSPAYMENTNACHSVALUE  ||  TINIMSATTRIBUTIONID  ||  VCHASSETNUMBER  ||  INTASSETTYPEID  ||  TINFRCATEGORYID  ||  CHRPERFORMANCESPECCATEGORY 
 ||  VCHDESCRIPTIONCLEARANCE  ||  SINSPECIALCLEARANCETIME  ||  CHRREASONFORCLEARANCE  ||  DTMEXTENDEDCLEARANCEDATETIME 
 || VCHINITIALNOTIFREPORTEDBY   ||  VCHINITIALNOTIFREPORTEDTO  ||  DTMTRANSFERINDATETIME  ||  TINFRCSENDERID  ||  TINFRCTRANSFERREDTO 
 ||  VCHCLEARANCEREPORTEDBY  ||  VCHCLEARANCEREPORTEDTO  ||  CHRCONSEQUENTIALINCIDENT  ||  TINIMPORTORIGINID  ||  TINIRF  ||  TININFRACOCONTACTID  ||  INTFRCNO  || 
  VCHFRCDESCRIPTIONFAULT  ||  VCHFRCDESCRIPTIONCLEARANCE  ||  DTMFRCNOTIFICATIONDATETIME  ||  VCHFRCINITIALNOTIFREPORTEDBY  ||  DTMFRCCLEARANCEDATETIME  ||  VCHFRCCLEARANCEREPORTEDBY 
 ||  BLNCHANGEDDETAILS  ||  BLNCOVERRISK  ||  DTMDATETOUSEFORCOVERRISK  ||  SINCAUSECODEID  ||  SINSERVICECODEID  ||  VCHCONNECTCONTRATORPARTY  ||  DTMCONNECTCONTRATORNOTIFIEDDT  ||  INTSERVICEPOINTS  ||  TINASSETGROUPID 
 ||  DTMDATERECORDADDED  ||  TINTYPEOFASSETNUMBERID  ||  VCHUNITNUMBER  ||  BLNREPORTRECEIVED  ||  TINKPI2ALLOCATIONID  ||  TINKPI2TIME  ||  VCHTRAINNO  ||  VCHINCIDENTREPORTMANAGER 
 ||  VCHINFRACOREFNO  ||  TINAREACODEID  ||  INTBRSSTATIONSERVICECODEID  ||  BLNSTATIONDISRUPTION  ||  TINCURRENTSTATUSID  ||  TINCURRENTATTRIBUTIONID  ||  VCHDISRUPTIONCURRENTLEVEL  ||  BLNCONTRAVENTION  ||  BLNTEMPUPDATE  || 
     BLNNACHSLEGITIMATEZERO  ||  VCHUSERRECORDADDED  ||  VCHSPEEDRESTRICTID  ||  INTDURATIONMINUTES  ||  BLNNACHSNOTRUN  ||  BLNENGOVERRUN  ||  INTENGOVERRUNSERVICEPOINTS  ||  BLNRPTRCVDWITHINTWENTYFOURHRS  ||  VCHEFOLDERID  ||  VCHLCFOLDER  || 
     VCHLASTUPDATEDBY  ||  DTMLASTUPDATED  ||  TINTIMEBANDID  ||  INTEXTENDEDTYPEID  ||  TINNACHSSTATUS  ||  INTHALLID  ||  INTROUTEID  || 
     INTPLSSURROGATEID  ||  INTLINESECTIONID  ||  TINRESTRICTEDSPEED  ||  INTAFFSTARTDISTANCE  ||  INTAFFENDDISTANCE  ||  INTLORID  ||  CHRDEGRADEDMODE 
 ||  BLNSERVICEHOURSOVERRIDE  ||  TINPLSCOLUMNUSED  ||  BLNONEWAY  ||  DTMMODELLEDENDDATETIME  ||  VCHSTARTPLATFORM  ||  VCHFINISHPLATFORM  ||  VCHVIAROUTE  ||  INTDETRAINMENTSTATIONID  ||  VCHOTHERSTARTPLATFORM  ||  VCHOTHERFINISHPLATFORM  || 
     VCHOTHERVIAROUTE  ||  BLNBOTHDIRECTIONS  ||  BLNSINGLEPOINTFAILURE  ||  BLNHOTSPARE  ||  VCHLOSSOFROUTEID  ||  BLNTDRREDUCTION );
     


any clue as how i can create this column
Re: calculated column in 10g r2 [message #570984 is a reply to message #570981] Mon, 19 November 2012 08:43 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
The thing is that when using concatenated values like this you can get a false result. What if for instance INTINTERNALID and VCHIMSNO have changed. INTERNALID used to be '12' and VCHIMSNO used to be 'cde'. The new values are '12c' and 'de'. You get the same concatenated result, but the column values are different. I would say that if you want a column like this you should create a view selecting this value from your table (in addition to the other columns if you need them).
Re: calculated column in 10g r2 [message #570985 is a reply to message #570984] Mon, 19 November 2012 08:51 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
I didn't check but are you sure that you can do this on a 10gR2 database?

MHE
Re: calculated column in 10g r2 [message #570987 is a reply to message #570985] Mon, 19 November 2012 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it can't be done, of course.

Regards
Michel
Re: calculated column in 10g r2 [message #570992 is a reply to message #570987] Mon, 19 November 2012 14:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
Well, it can be done in 10g, but you have to be creative:

SQL> select  *
  2    from  v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table tbl
  2    as
  3      select  *
  4        from  hr.employees
  5        where rownum <= 5
  6  /

Table created.

SQL> create index tbl_idx1
  2    on tbl(first_name || ' ' || last_name)
  3  /

Index created.

SQL> select  column_name
  2    from  user_tab_cols
  3    where table_name = 'TBL'
  4      and virtual_column = 'YES'
  5  /

COLUMN_NAME
------------------------------
SYS_NC00012$

SQL> column full_name format a30
SQL> select  first_name,
  2          last_name,
  3          sys_nc00012$ full_name
  4    from  tbl
  5  /

FIRST_NAME           LAST_NAME                 FULL_NAME
-------------------- ------------------------- ------------------------------
Donald               OConnell                  Donald OConnell
Douglas              Grant                     Douglas Grant
Jennifer             Whalen                    Jennifer Whalen
Michael              Hartstein                 Michael Hartstein
Pat                  Fay                       Pat Fay

SQL> 


SY.
Re: calculated column in 10g r2 [message #570999 is a reply to message #570992] Mon, 19 November 2012 23:45 Go to previous message
Michel Cadot
Messages: 60053
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using hide feature is not a good practice.
If you really want it, simulate the virtual column using a view upon the table (and hide this latter one).

Regards
Michel
Previous Topic: Error while deleting partition of a table
Next Topic: Best way to get several rows from SCD for each key
Goto Forum:
  


Current Time: Thu Dec 25 09:48:16 CST 2014

Total time taken to generate the page: 0.11300 seconds