SQL & PL/SQL :: Count Of Updates By Week
Feb 19, 2013
I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:
Record Date_Modified
155291141/23/2013
157277201/24/2013
152619321/25/2013
142263211/28/2013
140043421/28/2013
150050431/28/2013
148315761/29/2013
154364281/31/2013
148066382/1/2013
[Code]...
I would like to return:
Week Updates
43
57
69
77
84
Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.
how to go about this task.
View 7 Replies
ADVERTISEMENT
Apr 18, 2013
You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.
lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.
The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.
What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.
but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?
View 0 Replies
View Related
May 22, 2011
I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.
1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.
I am unable to figure out the proper way to implement the point 2 above in oracle database.
View 3 Replies
View Related
Apr 6, 2012
SESSIONID , returns the auditing session identifier am i right ?
my question is when oracle updating session id ?
SQL> show user;
USER is "SYS"
SQL> select sid,serial# from v$session where audsid=sys_context
2 ('userenv','sessionid');
[code]....
View 3 Replies
View Related
Nov 7, 2012
The updates occur always that a significantly change was done? (Like create, rename or drop datafiles and tablespaces)
Or the RMAN catalog owner must issue "resync catalog" frequently?
View 2 Replies
View Related
May 17, 2011
Check the below simple sql stmts:
create table tab_tab(col1 number, col2 number);
insert into tab_tab values(1, 1);
insert into tab_tab values(2, 2);
insert into tab_tab values(3, null);
[code]......
My question is why the table content didn't look like:
col1 col2
----------
1 2
2 2
3 2
4 3
5 4
I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!
View 12 Replies
View Related
Apr 8, 2011
Consider the statement below:
Update employee e
set e.dept_id = (select d.dept_id
from dept d
[Code].....
The above is not the exact code which I am executing but an exact replica of the logic implied in my code.
Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0
since there are no records matching for the condition:
(select d.dept_id
from dept d
where e.dept_name = d.dept_name)
So, I executed the statement:
select count(*) from employee e
where emp_id = 1234
and exists
(select 1
from emp_his ee
where e.emp_id = ee.emp_id)
and the result was 3 which is the same value returned by %rowcount.
why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.
View 7 Replies
View Related
Feb 17, 2012
I have a table of 3 columns:
SQL> show user
USER is "ANDREY"
SQL>
SQL>
SQL>
SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data:
SQL>
SQL> insert into a(key1 , key2) values (1 , 1);
1 row created.
SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a
set key3 = 'inactive'
where key2 = (
select max(key2)
from a a2 where a2.key1=a.key1
);
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1
2 IS
3
4
5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1;
PL/SQL procedure successfully completed.
SQL> select * from a;
KEY1 KEY2 KEY3
---------- ---------- ----------
1 1 active
1 5 incative
2 24 active
2 21 active
ORA-01034: ORACLE not available
View 10 Replies
View Related
Jun 7, 2013
Is this the correct forum to ask questions about Oracle CPU's?
I ran the CPU-JUL-2012 on a workstation for version 11.1.0.7, got Return Code 0 and "OPatch Succeeded".
Yet a Retina scan ran after shows no change....?
I've tried reading the availability document, but I have no experience at Oracle patching.
View 4 Replies
View Related
Dec 5, 2012
This problem went away for me in 4.2, but I am limited to 4.1 in production. The problem is this: I have three editable columns in my Tabular Form
1) I make a change to column 3 and press submit. The values in columns 1 & 2 (that were not changed) are overwritten with null during the update (submit). Column 3 is saved correctly.
I deleted my tabular form and re-created it thinking that I may have trashed something. But it appears that is the way it works. How do I prevent the unchanged editable columns from being overwritten with nulls?
View 2 Replies
View Related
Sep 26, 2012
I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.
The value doesn't always change but the scripts will still attempt to perform an update.
The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.
View 9 Replies
View Related
May 26, 2013
As my requirement is that to find week for particular date according to Arabic calender.As per Arabic calender first day for year starts from saturday.
so when i execute query :
select to_char(to_date('05/26/2013','MM/DD/RRRR'),'WW')
from dual;
Then it gives 21,but as per arabic calender it should show 22nd week.
getting result according to Arabic calender as it should return 22,because weak starts from saturday according to Arabic calender.
View 6 Replies
View Related
Jun 29, 2012
I had to get a query to give me all the Mondays of a IW week in a year. I think as per the IW weeks there are around 52.1 weeks in a year. So basically, I want the starting Monday of every week. I would then store those values in a variable and use them in APEX as column headers. Online I saw some samples not related with this, but where looping was done using the all_objects. Not sure if that is the right approach.
The problem is since this is not stored in any table, how can I loop like 52/53 times such that the output shows all the Mondays (date format) for a given year. Basically, the output would be 52/53 rows with a date(Monday of the week).
View 10 Replies
View Related
Oct 18, 2012
I need to change Monday like first day of week and not Sunday.
select to_char(to_date('01-01-2012'),'w') from dual
Result: 1
select to_char(to_date('02-01-2012'),'w') from dual
Result:1
and the idea is that 02-01-2012 must be the second week
View 3 Replies
View Related
Dec 31, 2012
I am running 10g on Linux, and last week someone messed up a database and it needs to be restored to 27 December. I am not a DBA, out regular DBA is on vacation till mid January,
I know we have archivelog on (I can see .arc files for the database), and I also have a backup of the actual data files and control files from when the database was first created. So in other words, I have all archive files and the initial data files. What i do not know is how to set the restore till December 27.
View 8 Replies
View Related
Nov 2, 2011
I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.
What I need is one for month end and one for last 7 days.
here's what I have so far.
select ap.name
to_char(ap.opendate,'MM-DD-YY') "Open Date"
from [databasname]
where ap.opendate between databasename-7 and databasename-1
I really don't want to change the date myself I want the system to know when the 7 days or month is.
View 4 Replies
View Related
Feb 5, 2011
I am creating report for this i want to make one query..
Query like this
My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...
Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...
View 10 Replies
View Related
Apr 5, 2011
How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.
View 8 Replies
View Related
Jul 14, 2010
How to display date every monday within date range
DEsc start date end date
XXXX 1/2/2010 31/10/2010
output
date every monday
View 25 Replies
View Related
Nov 9, 2012
I want to get the week of the year.
Conditions are:
1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013)
2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)
At many place I found the below solution:
select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;
But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).
View 6 Replies
View Related
Mar 7, 2010
I have two columns in table
sales_date
sales_amount
I need to find total sales for the whole last week.
Today is current week i.e. 1 (March 1, 2010 and March 7, 2010)
I need to find total_sales for the last week (i.e. Feb 22, 2010 to Feb 28, 2010)
I am unable to create logic for the same.
View 4 Replies
View Related
Nov 21, 2012
I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.
column 1: count of customers, count(customer_id)
column 2: Date
Not even sure if this is possible to select a date using the day name ?
Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .
View 4 Replies
View Related
Jan 16, 2012
select trunc(to_date('1201090000', 'yymmddHH24mi'),'DAY') from dual
will give output
08-jan-2012 00:00:00
which is sunday as per AMERICA nls_territory setting.
For my requirement i want to group from tue-mon for a week instead of sun-sat. modify the query to use the week start day as Tuesday.
View 2 Replies
View Related
Jan 20, 2011
I need some detail information regarding the CPM Patches released by oracle quarterly.
Is it compulsory to set the CPM patch when it is being realized. How to set or apply this patch. (either by runInstaller or OPatch utility i am not sure about this)
What is the difference between the interim & CPM patches.
View 3 Replies
View Related
Aug 30, 2012
How to update a form fields which taking a searched value from another table .
I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.
When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.
If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.
View 3 Replies
View Related
Feb 5, 2013
This is my stored procedure
I have below store procedure:
create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
View 12 Replies
View Related
Oct 25, 2011
how can create function or sql statement to show week of days without Friday and Saturday
View 13 Replies
View Related
Dec 9, 2012
I hava an requirement to get week number from particular date as per indian financial year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.
But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,
Date week_no
apr 01 to 07 1
apr 08 to 14 2
apr 15 to 21 3
.
.
.
.
.
Next year mar - 31 n
how to do this am waiting for your reply.
View 4 Replies
View Related
Feb 20, 2012
I have m display financial year calender(April-2011 to March 2012) and i want to display monday as a first week day.
View 3 Replies
View Related
Mar 12, 2013
I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.
If I CREATE MATERIALIZED VIEW LOG on that table.
View 4 Replies
View Related
