SQL & PL/SQL :: Updating Current Column With Previous Columns?

Dec 22, 2011

I need to update the current column with sum of the previous column values. Following are the creation scripts

DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(

[Code]....

Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.

I tried by using the following query

UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);

View 5 Replies


ADVERTISEMENT

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

Compare Current With Previous Row Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

CODEemp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL

Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

CODEWITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]....

For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:

CODE
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Compare Previous Records With Current?

Aug 29, 2013

I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).

source data

date1 iddate2 new_date
1/2/20111234/2/2011
4/2/20111237/2/2011
7/2/201112310/2/2011
10/2/20111231/2/2012
1/2/20121234/2/2012
4/2/20121237/2/2012
12/17/20121233/17/2013
3/17/20131236/17/2013

and I am expecting the out put like this

date1 id date2 new_date
1/2/20111234/2/20111/2/2011
4/2/20111237/2/20111/2/2011
7/2/201112310/2/20111/2/2011
10/2/20111231/2/20121/2/2011
1/2/20121234/2/20121/2/2011
4/2/20121237/2/20121/2/2011
12/17/20121233/17/201312/17/2012
3/17/20131236/17/201312/17/2012

how to achieve this with SQL

View 3 Replies View Related

Performance Tuning :: Compare Current Row Values With Previous One Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

emp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date
This table is yearly partitioned

I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id = 12345
[code]....

The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.

The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.

View 7 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

SQL & PL/SQL :: When Where Current Clause Is Used In Updating Or Deleting In Loop

Jul 1, 2013

when 'where current clause' is used in updating or deleting in loop.... is it required to write commit in the loop or it will auto commit?

View 2 Replies View Related

SQL & PL/SQL :: Compare Current Rows With Previous Rows

Apr 12, 2013

The Table having 3 columns

col0col1 , col2
P112
P112
P102
P113
P115

I want to retrieve the changes rows only.

Output like
col0col1 , col2
P112
P102
P113
P115

View 5 Replies View Related

SQL & PL/SQL :: Trigger For Updating All Columns Of A Table

Sep 12, 2013

I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.

create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);

[Code]..

are the below assignement of values will work ?

in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;

i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name .

View 8 Replies View Related

Converting Rows Into Columns And Updating A Table?

Apr 26, 2012

I have a table A, whose table structure is in the below format.

Table A

ID DESC VALUE
123 A 454
123 B 1111
123 C 111
123 D 222
124 A 123
124 B 1
124 C 111
124 D 44

Now i need to insert the data from this table to another table B, the sturcture of which is as below

Table B

ID A B C D
1234541111111222
124123111144

How do i frame a query to fetch data from table A and insert that into table B? I don't want to use max and decode combination. as it would return only single row for an ID. I need all the id's to be displayed.

View 1 Replies View Related

Avoid Updating Record If Columns Do Not Change?

Nov 29, 2012

is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:

UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12

if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying

View 1 Replies View Related

SQL & PL/SQL :: Cumulative Sum Of Previous Row In Same Column?

Jun 28, 2013

Is it possible to get cumulative sum of the same column? I am trying to get a value for COL6... it is dependent on the values of previous row

COL6 Formula:

IF COL2 = 'A' THEN
IF 100 - [SUM_COL6] > COL5 THEN
COL5
ELSE
(TRUNC(100 - [SUM_COL6] / COL4) )* COL4
END IF

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Selecting Value Of Previous Column In Case Statement?

Feb 20, 2012

Is there a way of selecting the value of a previous column in a case statement, without having to duplicate. E.g.

SELECT prod_id as Product,
Some complex calculation... as Total,
CASE

[Code]....

I do not want to re-write the same calculation in the else statement but just want it to be be the value of Total column.

View 1 Replies View Related

Reports & Discoverer :: Vertical Expansion Based Upon Info In Previous Column

Mar 19, 2012

I've a 6 column report where col5 is set to expand vertically (based upon comments) and works wonderfully. Col6 is a single character column, therefore does not expand so the report looks rather messy.

I would like col6 to expand in accordance to col5. Is it possible?

View 8 Replies View Related

SQL & PL/SQL :: Updating Column In Every Row Except For The First

Jan 16, 2012

I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:

SQL> select * from example1;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225

12 rows selected

I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:

SQL> select * from example2;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225

12 rows selected

I have done some searches and haven't seen any results.

View 12 Replies View Related

SQL & PL/SQL :: Updating Nth Row And Adding Column

Aug 7, 2012

I had created a table which have 100s' of entries in it.

create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***

[Code]..

1. In this table, how to update the Nth row, how can I do it

2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.

View 30 Replies View Related

PL/SQL :: Updating Column Value If Any DML Operations Done

Apr 12, 2013

I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.

ex:

id name
-- -------
1 xxxx
2 yyyy
3 zzzzz
4 pppp
5 rrrrrr

if i delete

delete from test where id=4;

then automatically.. "id" column values will again in sequence... like this

id name
-- -------1 xxxxx
2 yyyyy
3 zzzzzz
4 rrrrrr

note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....

ex : insert to test values(seq_name.nextval,'tttt');

id name
-- -------
1 xxxxx
.
.
.
4 rrrrr
5 ttttt

View 5 Replies View Related

Updating A Table Column Which Is XML Datatype

Nov 1, 2011

I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.

DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...

View 1 Replies View Related

Forms :: Updating BLOB Column

Mar 11, 2012

I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.

View 1 Replies View Related

Updating A Column Of A Table With 70,000,000 Records

Aug 1, 2012

I need to update column of a table with +,- 70,000,000 records.

If I perform an update it lasts......too much and does not finish!

View 6 Replies View Related

PL/SQL :: Is Updating A Non-indexed Column Slower

Oct 7, 2013

I have learnt that indexes slow down the DML operations. My question is specific to an update statement. Is it going to be slower if Im trying to update an indexed column on my table or it is slower overall (even when a non-indexed column is getting updated in the table) How does it behave in case of inserts & delete operation. 

View 4 Replies View Related

Updating Long Data Type Column

Aug 31, 2010

I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.

I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.

View 2 Replies View Related

Forms :: Formula Column Value Not Updating In Table?

Sep 5, 2011

I create the sample for master/detail form. In detail for prdcode,rate,qty,amount is there. When select prdcode it fetching prdcode,rate in a record and if you type the qty the amout will come based on formula(property) :qty*:rate.

It is available on screen. But when i store the data, in backend table the amount is be a null.

View 9 Replies View Related

SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

i am having a table with out pk along with data.

Now , i need to add one column to that table , and update this column with the sequence no

like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.

how can i do.

View 1 Replies View Related

SQL & PL/SQL :: Updating Table With Sequentially Changing Column Name?

Aug 9, 2012

I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.

begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;

View 6 Replies View Related

PL/SQL :: Updating A Column Randomly With Selected Values?

Sep 26, 2013

 We've an accounts table that basically represents hotel chains & brands. an example shown below. 

account_id chain_id brand_id  service1              
NULL     NULL          111              
NULL     NULL          122              
NULL     NULL           11

 Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.

There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,

chain_id  brand_id         
101     2011          101    
2012          102     2020 

Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg., 

account_id chain_id brand_id service1              
101          2011     111               101         
2011     122               102          2020     11 

so each account can be attached to only one chain_id and one brand_id.

View 2 Replies View Related

Does Column V$SQL.CHILD_NUMBER Related On Number Of Current Cursor Only

Jan 11, 2013

Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....

View 0 Replies View Related

Forms :: ORA-01461 When Updating A LONG-Column Via Trigger

Jul 5, 2010

I have a non-base-table item which I want to update in the pre-update trigger of the current block.

If the content of the field exceeds 4000 characters, i get the error message

ORA-01461: can bind a LONG value only for insert into a LONG column.

The code is

update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;

Workarounds would be,
1.) to delete the old dataset and insert the new one:

delete from tab
where tab.tabpk = :formsblock.tabpk;

insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);

or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.

Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related







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