SQL & PL/SQL :: Pick Up 3% Of Records Per Day?

Oct 12, 2010

create table test1(x number, y date)

insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');

[code]....

I need an sql which would pick 3% of the records per day

select count(*), y from test1
group by y
21 10/1/2010
14 10/2/2010
6 10/3/2010

So I need 3% of 21 (.63) randomely picked up for 10/1.

similarly 3% of total number of rows from 10/2, 10/3 to be picked up.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pick One Out Of Duplicates?

Jul 19, 2010

I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?

sample data:

IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30

For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.

View 2 Replies View Related

SQL & PL/SQL :: Pick Up The Data From Table 1

Jul 9, 2010

I have a reqmt that is little complex.

Example: (Real Data)

Table1 (Column Name)
Height
Width
Color

Table2
ID,Height, Width, Color, Remarks
1,2,3,NULL,NULL
2,NULL,NULL,2,NULL
3,1,2,3,4

What I require is pick up the data from table1 and for each row in table1, check the corresponding column to see if null. If null create a counter and add 1 to it.

I would only check for 1 record at a time in table2 (ID is input parameter in pl/sql) In other words, if my ID is 1, my count should be 1 (since COLOr is null in table2 for id=1 and color is a row in table1) if my id is 2, count is 2 (ht and width are NULL and are both separate rows in table1) if id is 3, count=0 since all fields have data even though some of these columsn exists as rows in table1.

View 25 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

I need to pick a value based on range like if the range is as below

[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]

Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.

View 2 Replies View Related

Forms :: Pick Single Value From Range?

Mar 19, 2012

i have to pick single value from the given range based on value entered by user, for example if user enter the length 2499 it should be considered as 2000 if he enters 2000 its 2000 or he enters 2501 it will return 2500 , i have written a huge if condition for this purpose in my program which looks very cumbersome,is there a simple to do this other than this method.

the code is as below.

[CODE]
IF
( :CSDO_STORE_RECD_LENGTH - SUBSTR(:CSDO_STORE_RECD_LENGTH,(LENGTH(:CSDO_STORE_RECD_LENGTH)-1),LENGTH(:CSDO_STORE_RECD_LENGTH))) =2000
THEN M_RCVD_LENGTH := 2000;
ELSIF

[code].....

View 9 Replies View Related

PL/SQL :: Pick All Continues Range Of Values Into One Bundle

Jul 11, 2013

I got a situation to develop a complex query. Here is the scenario. I have to pick all the continues range of values into one bundle. like in following example, rows having values between 20 to 25 should have one bunch and 27 to 28 should have another as there is no from or to value from 25 to 28. with q1 ( select 20 as Frm, 25 as tto from dualunion allselect 20 as Frm, 21 as tto from dualunion allselect 20 as Frm, 22 as tto from dualunion allselect 20 as Frm, 20 as tto from dualunion allselect 21 as Frm, 22 as tto from dualunion allselect 28 as Frm, 28 as tto from dualunion allselect 27 as Frm, 28 as tto from dual)select * from q1; 

View 10 Replies View Related

Forms :: How To Pick Excel File And Get Data In Column

Jun 18, 2013

How to pick excel data in oracle form 6i. and data show in forms column ?

View 1 Replies View Related

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

Jun 13, 2010

I want to make a trigger , to store values that are affected during any DML(insert,update,delete).

CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE

[code]....

View 8 Replies View Related

Reports & Discoverer :: Pick Latest Record Rank And Count?

Sep 1, 2010

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this

name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'

View 3 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

Feb 21, 2011

I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.

CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:

The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.

Query for anyone interested:

Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........

I have a separate query which returns a list of support resources.

select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'

What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.

(the res.unique_name field in the second query can be linked to the same in the first query)

Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.

View 18 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

SQL & PL/SQL :: How To Get Records For Max Value Only

Sep 21, 2010

I am working on the below query:-

SELECT adv_list.advisor_code advisor_code,
adv_list.available_wip, tot_score from(
SELECT advisor.advisor_code advisor_code ,
(wip.wip_limit - SUM (DECODE
(lead_status, 'F1', 1,0)))
available_wip,advisor.tot_score

[Code]...

The below query returns :-

Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10
00540272 5

My requirement is to get only those records that have the max(Tot_Score).

So I should get:-

Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10

I am trying to put MAX(TOT_SCORE) in the select list but its not giving the desied result.

I do not want to give it in the where clause as I will have to gain put the whole subquery again.

wherein I can get the MAX tot_score records only with minimum coding.

View 2 Replies View Related

SQL & PL/SQL :: Get AVG Of All Records

Sep 6, 2013

Type Specification :
CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT
(
-- AUTHOR : CLIVE.GREGORY
-- CREATED : 03-04-2010 14:44:02 14:44:02
-- Modified : S. Glass - Removed read function to increase performance

[code]...

Type Body :
TYPE BODY ArrayCounterSum AS
2
3
4
5

[code]...

So the output will be : 6_10_15_13_14 (Sum of all rows and return as an array)My Goal is to AVG the ROWS and retrun it into array. so what change should I make in above code in order to get the AVG of all records.

View 25 Replies View Related

Get Latest Records?

Jun 9, 2011

I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...

UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1

It then inserts the new latest record...

INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (<vehicle_id>, <x pos>, <y pos>, SYSDATE, 1)

Is it possible for me to end up with 2 latest records?Consider this scenario...

Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1)
Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)

I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route

View 4 Replies View Related

Fetching Last N Records?

Jun 5, 2012

oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE MACHINE_M (
IDVARCHAR(20)NOT NULL,
NAMEVARCHAR(20)
)

[Code]....

Now when there records in READING_DWR are 1 lakh, i am getting the o/p in 10 sec.
However when there are bulk records, it is taking 3 to 4 min.

Is there any way to improve my query performance?

View 1 Replies View Related

Edit Records In 10g EM?

Mar 26, 2009

I just loaded the 10g client EM on my Windows workstation to connect to our 10g database.Everything works great except I dont see any place where I can edit records. I have to use SQL Plus to edit records but was hoping to use a GUI like I had with 9i client OEM.Before we had 10g, I used to have the 9i OEM and it let me edit records in 9i OEM where I could add, delete and update records with a user friendly interface.

how I can edit records in 10g EM?

View 3 Replies View Related

How To Get Alternate Records

Oct 17, 2006

how to retrive alternate records from the table in the answer table the first row wil be in upper case and the second row wil be in lower case ...like wise wat is the querry

View 1 Replies View Related

SQL & PL/SQL :: All Invalid Records

Jun 12, 2013

I have the following sample data:

Sample Data

WITH DATA AS
(
SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL
UNION ALL
SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL
union all
[code]......

Query Result

10001-JAN-201230-JUN-2012
10001-JUL-201231-JUL-2012
10031-JUL-201205-AUG-2012
10001-AUG-201215-AUG-2012
10017-AUG-201231-AUG-2012

Expected Output

10031-JUL-201205-AUG-2012
10017-AUG-201231-AUG-2012

The above mentioned output is produced by using the following business rules:

-- row no 1 = Valid records
-- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records
-- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record
-- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records
-- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.

Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.

Summary is that there should be 1 day gap between termination date and next effective date as well as effective date
is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.

View 15 Replies View Related

SQL & PL/SQL :: How To Get Common Records

Feb 10, 2013

I have a situation as follows.

Table1 has 10 columns as col1,col2,col3,col4,...col10

Table2 has 5 columns as col1,col2,...col5.

col3 and col4 are common in both tables.I am trying to get common records from both tables.How can I get them.

View 3 Replies View Related

SQL & PL/SQL :: How To Add Some Records Between String

Apr 6, 2012

i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like

sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>

in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.

where <Insurer> and <I-Care ID> records are in two different tables

where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text

View 3 Replies View Related

SQL & PL/SQL :: Constraints On RECORDS?

Mar 21, 2011

How to create any constraints other than NOT NULL in PLSQL RECORD.

View 5 Replies View Related

SQL & PL/SQL :: Collection Of Records

Sep 29, 2010

I have been doing some code in collection for testing. I have been doing the below but getting the error.

SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......

ERROR at line 15:

ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored

I followed the below link

[URL].......

View 5 Replies View Related

SQL & PL/SQL :: Query For The Top 10 Records

Apr 22, 2011

I need to find the most recent rows (top 10) in a table... I have about 300 tables so the fields are different, is there a way to generically find it?

View 17 Replies View Related

SQL & PL/SQL :: Exclude Records

Sep 19, 2011

I have a table zTEST with several products and ID's. Now I want to exclude all products which are in table zTEST_EXCL and have ID's up to ID+2. The Result is then in table zTEST_RESULT.

What is the smartest way to do this?

create table zTEST
( PRODUCT number,
ID number);
insert into zTEST (PRODUCT, ID) values ( 1 , 1 );
insert into zTEST (PRODUCT, ID) values ( 1 , 2 );
insert into zTEST (PRODUCT, ID) values ( 1 , 3 );
insert into zTEST (PRODUCT, ID) values ( 1 , 4 );

[Code]...

View 7 Replies View Related

PL/SQL :: Find No Of Records?

Apr 27, 2013

How to find out the no.of records in a cursor for loop without using counter variable. can i use %rowcount inside for loop?

declare
  cursor dept_cur
  is
    select deptno,dname,loc
    from dept;

[code]...

View 10 Replies View Related

PL/SQL :: Updating Records With Different IDs

Jun 6, 2012

Im looking for the posibility to update some records using new id with the column values with another id

example

the table contains these records:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

and I would like insert the same gross and net column values of ids 7 to 16 into columns with the ids 40 to 49 in the same order. therefore I would like to obtain the result that I describe below:

id    gross      net

========================
7     0,1     0,0507749
8     0,2     0,1015499
9     0,5     0,2538748
10     0,83     0,4214
11     0,85     0,4315873

[Code]....

View 4 Replies View Related

PL/SQL :: Get All Invalid Records

Jun 12, 2013

Oracle Version : Oracle Database 11g Express Edition Release 11.2.0.2.0 - ProductionI have following sample  data:

Sample DataWITH DATA AS( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE
from DUAL           
UNION ALL           
SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012'
FROM DUAL          
UNION ALL  SELECT '100' .

[Code]....

Business Rules: I have to get all invalid records according to the below business rules.

- row no 1 and 2 are valid records because row no 2 effective date start after the termination date of row no 1
- row no 3 is invalid record because effective date start with termination date of row no 2
- row no 4 is valid record because effective date is after or greater than previous valid record termination date that is row no 2 (31-jul-2012).
- row no 5 is invalid record effective date is smaller than previous termination date
- row no 6 is valid record because effective date is greater than previous valid termination date that is row no 4 .
- row no 7 is invalid record because same data already exist in row no 6
- row no 8 is valid  because effective date is  greater than previous valid record  that is row no 6 termination date. 

View 17 Replies View Related

SQL & PL/SQL :: See Only Duplicate Records But Not Able To Get Through?

Dec 22, 2010

i am using this query to see only duplicate records but not able to get through

select * from
emp
where rowid NOT IN
( select max(rowid) from emp GROUP BY job_id)

View 7 Replies View Related







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