SQL & PL/SQL :: Calculated Column In 10g R2

Nov 19, 2012

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 calculated 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
[code].........

View 5 Replies


ADVERTISEMENT

Server Utilities :: Calculated Column Population Using Sql Loader?

Aug 1, 2012

I want to insert calculated data while loading the data from sql loader.

eg let say column INTERNAL_TRANSACTION_ID,WAIT_TIME,MESSAGE_GUID,TRS_SIZE,RETURN_MESSAGE_GUID are null in input file then i want to populate column DEV_FLAG as 'U' ELSE 'D'

My control file looks like below but doesn't have DEV_FLAG.

load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR

[code]....

View 2 Replies View Related

How Memory Is Calculated

Nov 16, 2012

I am not a major member of performance tuning area.Performance team is saying DB utilizing high memory. DB team is telling DB utilizing low memory.

How it was calculated here ?

View 3 Replies View Related

SQL & PL/SQL :: Calculate With Calculated Fields?

Feb 24, 2010

can I use calculated fields (b) for another calculation (c) in the same select?

select 10*a as b,
10*b as c
from tab

The code gives an error, but how can I do this?

View 12 Replies View Related

Forms :: Continuous Update Of Calculated Field?

Aug 6, 2013

I have seen in probably VB or Dot net software being used at some premises that there "formula computation is live and interactive".

What i means that suppose a cashier has made invoice having total of 83$. Mow in the text box for entering amount received, the cashier is about to enter 100 but surprisingly the returned value/remaining value (total invoice - amount received) is being computer LIVE.

This is what happens.

1) The cashier entered 1 and the remaining value instantly showed 82 (83-1)
2) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 73 (83-10)
3) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 17 (83-100)

Hence a continuous updation/changing of the remaining value on every key stroke is observed.

View 3 Replies View Related

Forms :: Populating Form With Calculated Values

Aug 19, 2010

I have a form in which the fields are calculated fields. So i need to populate the form after calculating the values based on a database columns. For example if i have a table as below

COL A | Col B | Col C | Col D
2 3 4 5
3 4 5 6

I have 3 fields on my form which i need to populate such that field1 maps to col A. Field2 value is COL B * COL D for that value of COL A. Field 3 value is COLB * COL D for that value of col A.

I tried something like this

PROCEDURE NAME IS

cursor c1 is SELECT * FROM TABLEL;
begin
go_block('Block1');
clear_block(no_validate);
for i in c1 loop
:BLOCK.field1 := i.COL_A;
[code].......

this fetched all the records for field1 but for field2 and field3 it only calculates and populates the field row.

View 1 Replies View Related

How Kernel Parameters Values Calculated In 10g

Feb 9, 2013

*10g Oracle installation on OEL*

How  Kernel Parameters values calculated in 10g

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
+# semaphores: semmsl, semmns, semopm, semmni+
kernel.sem = 250 32000 100 128
[code].......

Run the following command to change the current kernel parameters

/sbin/sysctl -p

**  "/etc/security/limits.conf"  **

*               soft    nproc   2047
*               hard    nproc   16384
*               soft    nofile  1024
*               hard    nofile  65536

My question I think 32 bit or 64 bit OS calculation will be same only. I want know how it was calculated ?

View 3 Replies View Related

Forms :: Frm-41375 - Cannot Set Queryable Property Of Calculated Item

Jun 10, 2013

frm-41375: cannot set queryable property of calculated item :emp.sumsal.

View 8 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Application Express :: Hyperlink Column With Link Parameters As Region Column Values

Apr 10, 2013

On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.

I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.

So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:

a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?

The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.

---

"Application Express 4.2.1.00.08"

View 2 Replies View Related

Reports & Discoverer :: REP-1241 Circular Column Dependency Originating With Column?

Jul 13, 2010

I receive the error,

REP-1241 Circular column dependency originating with column 'cenvat_opening'

I have the following return statement for a column total_credit;

function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;

Now i am using this total_creditFormula for the error formula column 'cenvat_opening'

function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN

[code]...

here the formula column 'total_credit' has the following return statements;

RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)

so how can i use this formula column? is there any other option to return the value in the same column.

View 6 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

Reports & Discoverer :: How To Make Name Of Column Appear As Many Times As Column On Page

Nov 21, 2010

This is what i want to do:

I have a basic report looking like this:

<Column name>
value 1
value 2
value 3
.
.
.
value n

Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.

After these changes my report looks like this:
<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

What i want is my report to look like this:

<Column name>-------<Column name>------------<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m

View 4 Replies View Related

Server Utilities :: Mapped 100th Column From CSV File After 5th Column

Sep 10, 2012

I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.

OPTIONS (ERRORS=100000000)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
[code]....

View 10 Replies View Related

Application Express :: Column Link Where Column Number Changes With Query

Feb 11, 2013

We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report

<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012

[Code]....

The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.

Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)

ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.

So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.

View 0 Replies View Related

Application Express :: Can Pass More Than 3 Column Values With A Column Link

Jun 19, 2012

I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?

View 3 Replies View Related

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

Jul 19, 2013

CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......

actual output of the below query, but i want to display in different way

select * from type;

C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E

The expected output should be like this below, how to write a query or which built in function used to get the below result,

Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M

View 4 Replies View Related

SQL & PL/SQL :: Row To Column / Column Data Can Be Varied From 1 To Any Number Of Words

Mar 21, 2013

I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.

eg

id description
78664 Pumps Alarm from CAMS RTU154

In the above example this column has 5 word so i want data in 5 rows like below

78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

This column data can be varied from 1 to any number of words.

View 11 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

PL/SQL :: Bitmap Index On Number Column X Char Column?

Jul 23, 2013

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:  

select ass.column20,  ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'    

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO

View 7 Replies View Related

SQL & PL/SQL :: Altering Virtual Column To Real Column?

Oct 4, 2011

Is there a way to alter an existing virtual column to set as a real column without dropping it and recreate (i have index on it, and i don't want to recreate them).

View 2 Replies View Related

SQL & PL/SQL :: Converting Number Column To Date Column

Dec 25, 2012

I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:

alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

View 12 Replies View Related

SQL & PL/SQL :: Column Length Versus Column Size

Oct 6, 2011

if one of the columns is given as

ABC varchar2(10)

can we tell anything about the size of the data in bytes that this column going to hold.

View 3 Replies View Related

PL/SQL :: Difference Between Days Column And Day1 Column

Oct 25, 2013

I used this query: 

SELECT to_char(return_date_time,'dd/mon/yyyy hh24:mi:ss') Test,DECODE ('I', 'I', DECODE (1, NULL, NULL, 0, NULL, SYSDATE + 1)) Days,DECODE ('I', 'I', SYSDATE + 1) Day1FROM fm_curr_locnWHERE patient_id = 'DU00002765'  output:  TestDays Day126/oct/2013 00:00:00 26-OCT-13 10/26/2013 3:06:59 PM

 I want to know the difference between Days column and Day1 column and why the days column didn't show the time .

View 13 Replies View Related

PL/SQL :: Derived Column Data As New Column In SELECT

Jul 31, 2013

I have a INSERT query which is happening with a SELECT query.

 ===================================================

INSERT INTO tbl_fact_effort_lvl_data (  ...............       )                       
SELECT ria.report_id,report_status:  :,
((SELECT lov_num_val                                   
FROM tbl_reference_data                                
WHERE lov_type = 'FREQUENCY'   ) * (SELECT SUM(pph_task)
FROM tbl_ri_process                                      
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS   ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id                                                                                                      
FROM tbl_fact tf  LEFT JOIN ......... ;

==================================================== 

So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.

View 8 Replies View Related

SQL & PL/SQL :: Split Column Value To Two Different Target Column

Dec 26, 2011

I have a table with date_field and ID number

Now based on the id number I have to redirect the date_field value to two different column

I/p

CASE_DT DT_TYP_CD
2006-07-01 00:00:001
2007-07-01 00:00:001
2008-07-01 00:00:001
2007-07-01 00:00:0011
2008-07-01 00:00:0011
2009-07-01 00:00:0011

Now I want the case_dt value to two diff column PL_Date and PL_Aniv_dt

If dt_typ_cd is 1 the case_dt value will go to PL_Date and if dt_typ_cd is 11 the case_dt value will go to PL_Aniv_dt

O/P
PL_dt PL_Aniv_dt
07/01/2006 0:0007/01/2007 0:00
07/01/2007 0:0007/01/2008 0:00
07/01/2008 0:0007/01/2009 0:00

View 8 Replies View Related

SQL & PL/SQL :: How To Update Data In A Column From Another Column

Dec 28, 2010

I was wondering how can I do below statement in oracle

update table1 t1 set t1.column1 = t2.column2
from table2 t2 inner join table3 t3 on t3.column3=t2.column4
where t3.column5 is null

I tried read up merge and update, but not really understand how the syntax works in oracle.

View 2 Replies View Related

SQL & PL/SQL :: Getting Column Data As Column Heading?

Apr 20, 2012

I would like to have column data as column headers.

Tables:

skill_table
SKILL_ID | NAME
3431060 | Stomach
3431064 | Hand
3437806 | Finger

localnode_table (which actually has the order/alignment (like what is next and what is previous) of the name from skill table.

NODE_ID | PREVIOUS_ID | NEXT_ID
3431060 | | 3431064
3431064 | 3431060 | 3437806
3437806 | 3431064

How to make it appear like:

Stomach | Hand | Finger
3431060 | 3431064 | 3437806

View 3 Replies View Related

SQL & PL/SQL :: Add New Column To Table Between Column List?

Jun 16, 2010

How to add column 'col_new' in below table after the column 'col1'?

Table name: ofq1
table structure.
columnname datatype
------- -------
COL1VARCHAR2(20 BYTE)
COL2NUMBER
COL3DATE
COL4DATE

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved