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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
Mar 21, 2011
How to create any constraints other than NOT NULL in PLSQL RECORD.
View 5 Replies
View Related
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
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
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
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
View Related
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
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
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
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
Oct 10, 2013
My data is like this.
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTAmountFor_qtrFor_year237898270KirtikarWESTEnterprise100000Q1 2012237898720KirtikarWESTEnterprise310000Q12013237898720KirtikarWESTEnterprise400000Q22013237898720KirtikarWESTEnterprise2500000Q32013237898720KirtikarWESTEnterprise520000Q42014
Required Output:
PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTQ1_AMTQ2_AMTQ3_AMTQ4_AMTYear237898720KirtikarWESTEnterprise31000040000025000005200002013-2014237898720Kirti
[code]......
View 11 Replies
View Related
Jul 12, 2012
i'm using the below query to update a VOTER table with over 15million records but it's taking ages to finish. i am using 11gr2 on linux
the query:
MERGE INTO voter dst
USING (
SELECT voterid,
pollingstation || CASE
WHEN ROW_NUMBER () OVER ( PARTITION BY pollingstation
[code]........
View 6 Replies
View Related
May 28, 2008
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
View 7 Replies
View Related
Oct 28, 2010
Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.
I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.
After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.
The problem is: how can I not use a temporary table. I've search through online about using nest tables. but somehow I just could not get it working.
how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.
View 3 Replies
View Related
Apr 21, 2011
I have requirement like we need to send records from one table to another table. for example if i have 4 records in Table A , first i need to send only 2 records to Table B then again rest 2 records to the Table B.
View 10 Replies
View Related
Aug 10, 2011
I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".
which is the best option to make most efficient search --
1) rownum < 201
2) row_num()
3) rank/denserank etc
View 3 Replies
View Related
Sep 18, 2013
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
View 4 Replies
View Related
Jun 27, 2010
i am using oracle 10g. and my database was in arabic and when i press Ctrl+L the LOV was displaying all the records what i select. but when i want filter the lov by customer name it was not filtering.
View 2 Replies
View Related
