PL/SQL :: Between Certain Moving Dates
Jun 25, 2012
I'm trying to run a report that has a moving date and I need to find data that's within 12 months of that certain date.
So for example... customers come in everyday all year long. I wanted to find the number of unique customers in a year. But the year is moving... So 1 year from 1/15/2011 is 1/14/2012. And 1 year from 1/16/2011 is 1/15/12. So I had something like this but doesn't quite work..
SELECT ...
NVL(COUNT(DISTINCT CASE WHEN TX.DATE_OF_FIRST_VISIT BETWEEN TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY') AND ADD_MONTHS(TO_DATE(TX.DATE_OF_FIRST_VISIT,'MM-YYYY'),12) THEN (TX.CLINIC_ID||TX.PATIENT_UNIQUE_ID)END),0) AS "YEAR_1"
View 10 Replies
ADVERTISEMENT
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
Oct 22, 2003
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
View 14 Replies
View Related
Dec 19, 2006
My company is thinking of moving from ora 8i to Postgre? What's your opinion on this?
Note: Our database is quite large, over 120mill records, and about 20-50 clients constantly connected.
View 7 Replies
View Related
Jul 25, 2013
I need to move everything from database 'X' to database 'Y' (assume: both are Oracle 11.2).Which should be the most appropriate way to achieve that?I thought of Transportable Tablespaces- URL..... but I'm worried about it's limitations especially this:SYSTEM Tablespace Objects - You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences This means all PL/SQL code- Packages/Procs/Functions will be affected.
View 27 Replies
View Related
Oct 31, 2012
I read this article:
[URL]......
Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?
View 3 Replies
View Related
Mar 7, 2011
In my Production DB. 5 Datafiles created in same tablespace. Datafile size is of 25GB. Data stored in all Datafile. Data is just 5GB in all datafile. I want to move data from 5 datafiles to single or couple of datafiles.
This is in Oracle 11g.
View 2 Replies
View Related
Feb 4, 2013
I need to prepare script to move all objects from one tbs to another tbs. Should I move all the objects individually using "alter table" Command. I got all the objects information using "DBA_SEGMENTS" view.
I have more number of tables,indexes in that tablespaces.
I can not use exp for tablespace backup.
View 4 Replies
View Related
Jan 21, 2013
I have imported data into database using sqlloader into flat table. Now I need to move the data from this table to another table. This is production system and I must keep it online. So I decided to make script that will move data in small chunks and commit frequently to avoid waits and table locks.
Regarding the script I have question. I can to the bulk load of rowids. Is it possible to optimize the insert and delete in similar way instead of doing insert/delete in loop for each rowid ?
declare
type t_rowids is table of rowid;
rowids t_rowids;
begin
loop
select rowid bulk collect into rowids from ims_old.values_f2 where rownum < 1000;
[Code]....
View 6 Replies
View Related
Dec 13, 2011
One of our auditing recommendation is to move table AUD$ to a separate tablespace from system. Why this recommendation is important and how to do this action ?
View 2 Replies
View Related
Oct 30, 2012
We need to move schema from one tablespace to another tablespace.
we are going to use following method:
alter table <tab> move tablespace <new_tbsp>;
my question is do we need to disbale primary keys and foreign keys before move and enable them at end.
View 4 Replies
View Related
Nov 20, 2012
I need to find information on the cost and difficulty in implementing the change from MS Access to Oracle.
View 3 Replies
View Related
Jul 3, 2013
I've moved a package to a new schema and all the packages in the original schema that reference the moved package now fail to compile. The moved package has had a public synonym created and the execute privileges assigned to the original schema by role. what am i missing? Using 11gR2 version 11.2.0.3.0
View 2 Replies
View Related
Feb 7, 2013
this query
SELECT
shrtgpa_pidm,
shrtgpa_term_code,
sum(shrtgpa_hours_earned) over (
partition by shrtgpa_pidm
order by shrtgpa_term_code
[Code]....
retrurns this rsult
82724 201010 4.5 201010
82724 201020 9 201020
82724 201110 13.5 201110
82724 201120 17.5 201120
82724 201310 20.5 201310
I need to be able to displayu the 4.5 in 201020 9 in 201110 in another words I need to move the results one term down
how I can do this?
I am thinking in a sequence? so 201010 will be seq 1
201020 will be seq 2
201110 will be seq 3
etc..etc..the I can do an update
Can this be done with aggr sql func? If not how I can add a sequence in the query It needs to break by shrtgpa_pidm
I need to end with these results
201020 4.50
201110 9.0
201120 13.50
201210 17.50
201220 21.50
here is the code to withe a table with data
create table testgpa
(
testgpa_pidm NUMBER(8) NOT NULL,
testgpa_term_code VARCHAR2(6 CHAR) NOT NULL,
testgpa_hours_earned NUMBER(9,3) NOT NULL
)
[Code]....
View 2 Replies
View Related
Mar 12, 2012
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS
PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;
[code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
View 1 Replies
View Related
Mar 9, 2012
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
View 1 Replies
View Related
Oct 7, 2012
I have a requirement to move the databases to a new server.
Existing set up:
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.2
OS - UNIX AIX-5.3
New Server (setup):
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.3
OS - UNIX AIX-6.1
My query is what is the best way to move the database to new server(UNIX AIX 6.1)?
a) can i upgrade the database to 11.2.0.3 before moving to new server or its better to move the database in 11.2.0.2 and then upgrade there?
b)is export and import of database can work for moving this?
c) RMAN backup and restoration can be done for moving databases?
d)simply moving all the related files to new server and start upgrading it from there?
View 8 Replies
View Related
Aug 10, 2013
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
View 17 Replies
View Related
Sep 23, 2010
I need to move database ORCL into our existing central database CNTR (both are on same OS and oracle version) I started exp each schema from ORCL and imp in CNTR.
But there is one schema EXMP in database ORCL which also exists in CNTR database with same tables, indexes . The data under schema EXMP in ORCL should be added to schema EXMP in CNTR.
View 9 Replies
View Related
Mar 13, 2013
The steps to move OMF files in ASM. I tried the following and was not successful.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/system.357.809972853"
datafile 2 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/sysaux.363.809972837"
datafile 3 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs1.365.809972737"
datafile 4 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/users.361.809972859"
datafile 5 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs2.360.809972761"
datafile 6 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs3.359.809972787"
datafile 7 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs4.358.809972811"
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/13/2013 16:30:05
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> alter database open;
so switch worked reset logs says can't use it there so I just try to open and it just hangs.
View 1 Replies
View Related
Feb 21, 2013
We have a p/slq procedure that reads a *.txt file using the UTL_FILE package. The contents of the file are then inserted into a database table.
At the end of the procedure we close the open file using UTL_FILE.FCLOSE.
There is a program (non-oracle)that attempts to move the file to a new location after being read into Oracle. The problem is that the application cannot move the file as the file is locked. ie message displays that the file is open and cannot be moved to a new location.
Is there anything else that we are missing besides the UTL_FILE.FCLOSE.
View 5 Replies
View Related
Mar 9, 2012
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table. It must happen in triggers, but I keep getting the mutating error. I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
View 3 Replies
View Related
Jul 2, 2012
I've inherited a DB where they are going to do a restore this weekend.
The current DB admin is using a db restore (not a duplicate). RMAN with no catalog. The current issue is that the DB restores fine, but when we do a delete obsolete after our backups it's asking if we want to delete the data files.
View 13 Replies
View Related
Aug 21, 2008
Oracle Data Replication. I am using Oracle 10g release 10.1 and I want to do replication of my data from one machine to another machince.
View 2 Replies
View Related
Dec 19, 2012
"All data you create in this tablespace will be encrypted using an AES256 encryption key. You cannot encrypt an existing tablespace. To encrypt data, first create an encrypted tablespace, then use alter table move, CTAS or datapump import to move your data into the encrypted space. Remember to drop the old tablespace BUT not including datafiles. Use an OS schred program to remove the old datafile. If you are on ASM you may use the including datafiles option since you can’t schred files from the OS inside an ASM instance."
But i want to know why we should NOT drop the including datafiles, when dropping tablespace (so 'drop tablespace my_tbs including contents and datafiles'). So what option should we use when dropping tablespace?
Why we should use OS capabilities to remove the datafiles?
What happens if i remove the datafile when i drop the tablespace?
View 13 Replies
View Related
Oct 19, 2011
We're currently in a situation where the primary database server fs size does not match the standby database server fs size.
Standby database filesystem is almost 100% utilized, and we suggested to move some of the datafiles first to avoid threshold alerts and archive gaps.
Now, if we're gonna move datafiles on the physical standby, I believe the process would be stop managed redo apply -> shutdown standby -> OS move -> startup mount -> alter rename -> start managed redo apply. Is this correct? If not, how?
Also, would it have an effect if the controlfiles of primary and standby do not match because of the movement?
View 1 Replies
View Related
Dec 15, 2010
We have Root disk on all the unix servers and is mirrored using SVM using the internal disks. Also VxVM 5.0 MP3 is used as the volume manager to manage the SAN space allocated to the servers. On some instances, SVM is also used for allocating filesystem for application and database
We are required to move all the files from SVM to VxVM.
In the process we are Copying our Oracle binaries from SVM to VXVM.I am planning to use the CPIO command to do the same.Once i move to VXVM (Veritas Volume Manager), i will bring down the SVM.
View 2 Replies
View Related
Feb 1, 2013
I have create Master block and a Detail block.When i am clicking on the Master block, then details records are displaying.But when i m moving from 1st record of detail block to 2nd record of master block, then detail block's data is not not displaying.It just firing the validation of detail block's first field.
when from Detail block (Cursor on the field Mapping code field) clicking on the 2nd records i.e Ord.No. =2 , at that time validation is getting fired.)
What i really wants that if click on the 2nd record of Master, automatically records should display in detail block.
View 3 Replies
View Related
Jun 28, 2011
I have a problem(illega use of LONG) while moving image data from SQL Server 2005 database to Oracle10G table in BLOB.
insert into table
select [[mailto:.....tablename@odbc SQL|.....tablename@odbc_SQL]]
I am using HS connection and DBLink.
View 10 Replies
View Related
