SQL & PL/SQL :: Using Rownum To Retrieve Few Records?
Mar 29, 2010
select rownum, CATR_ID, CAT_ID, CATR_REG_COPY, CATR_REG_LABEL, CATR_ACQUIRED_DATE, CATR_REG_DATE, CATR_MEDIA_COMMENTS, CATR_WITH_DIGITAL,
CATR_ORIGINAL, CATR_LINK, CATR_CREATED_BY, CATR_CREATED_DATE, CATR_MODIFIED_BY, CATR_MODIFIED_DATE, CATR_CHECKOUT, Available,
CATR_RETURN_DATE, LOCN_ID, LOCN_SITE, LOCN_LOCATION, MTYPE_GROUP, MTYPE_NAME, ACCESS_LEVEL, DESCRIPTION, CAT_TITLE, CAT_DESCRIPTION,
CATEGORY_ID, CAT_AUTHOR, CAT_PUBLISHED_DATE, CAT_PUBLISHER, CAT_EVAL_RELEVANT_KEYWORDS, CAT_REG_NUMBER, CAT_REG_SUBNUMBER, U_NAME
[Code]..
There are over 1500 records, but this query does not return any row. If i change rownum >= 100 to rownum <= 100 it returns first hundred records though... What is wrong here?
View 12 Replies
ADVERTISEMENT
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
Apr 13, 2011
I want Query on this table. EXAMPLE TABLE:
SENDER RECEIVER AMOUNT
A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000
Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.
CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.
OUTPUT LIKE:
USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000
View 14 Replies
View Related
Jun 19, 2013
I need function to pick the record from DB random manner.For example, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
View 12 Replies
View Related
Nov 25, 2010
I wrote the function witch returns some information.
function get_cust_info (v_msisdn integer) RETURN sys_refcursor
IS
curs sys_refcursor;
BEGIN
open curs for 'select first_name, last_name, street, town
from the_table where MSISDN = :1' using v_msisdn;
RETURN curs;
end
How to call this function to write result into table? I just want to write function which returns more Varchar.
View 6 Replies
View Related
Oct 8, 2010
I have to write a file using the UTL_FILE that needs to look like the one below. I am getting the righ results but I am getting duplicates, If I have more than one records retrieve in the Invoice_data_cur cursor(for the same pidm) I got the right results, but I got duplicate data, in another words if I retrieve 3 records from the Invoice_ data_cur, I got the same record write to the file 3 times, but the right results. If I only retrieve 1 record in Invoice_ data_ cur , I only get the results of the in invoice_detail_charges_cur I don't get the results of the invoice_ detail_ payments_ cur:
H xxxxxxxxxxxx
I 00399999 10/02/201000000000005000000000000500Jane Smith
D 00366899 Current charges Fall 201010/02/2010Study Abroad Fee - Semester 0000000001200
D 00366899Current charges Fall 201010/02/2010France School Abroad 0000000011200
[code]....
I am getting data in all the cursors..
View 25 Replies
View Related
Jun 19, 2013
I need function to pick the record from DB random manner.
For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
If user inputted value is 10 means, it should display one record between 1 to 10.
View 1 Replies
View Related
Dec 18, 2012
I have a following requirement in SQL -
Requirement -
I need to list the purchase Ordrers which are not closed.
my sql query should pull if the PO is not closed in 30,90 and 150 days.
It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.
View 1 Replies
View Related
Dec 28, 2010
I have a big database, I want to retrieve its records in several tries; first I use "select * from dbname where rownum <1000" but for 2'th try how can I get next 1000 records but not previous records?
View 1 Replies
View Related
Mar 17, 2010
i have a doubt in building a query.
I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010
If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
View 12 Replies
View Related
Jul 20, 2010
Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.
View 12 Replies
View Related
Jun 17, 2012
i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding
DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....
just caling here EMPNO and :v_empno is fields on form...
View 1 Replies
View Related
Nov 22, 2012
I tried to make my Query as simple as possible but also it contains the problem:
SELECT A.Id,A.Attachment,A.CreateDateTime,A.No
,rownum as RowNumber
FROM (
SELECT A.Id,A.Attachment,A.CreateDateTime,A.No FROM GOutgoingLetter A ,
(
SELECT B.Id, B.createdatetime as of0 FROM GOutgoingLetter B
WHERE
Exists
[code]...
when I use Both Order By And RowNum in my Query, Two columns of final Select Are Null: NO & Attachment. whereas this Columns aren't empty.when I comment each one of "ORDER BY B.of0" or ",rownum as RowNumber " everything is correct!!
View 1 Replies
View Related
Jan 15, 2013
In my sql query, how can i fetch the row with max row count? the query has around 10 columns.
View 2 Replies
View Related
Apr 4, 2010
When i try to execute a query, which is organised as the below example, it retrieves data..
select * from (
select col1, col2, col3, col4....coln
from TABLE_ONE left outer join TABLE_TWO
-- some conditions and group by clause
order by 1 asc
)
where rownum <=1000;
Again if I use Column alias in the ORDER BY clause col1, the query won't retrieve data.
Also If I use ORDER BY 4 instead of ORDER BY 1, the query wont return data...
select * from (
select col1, col2, col3, col4....coln
from TABLE_ONE left outer join TABLE_TWO
-- some conditions and group by clause
order by 4 asc
)
where rownum <=1000;
The whole issue revolves around the inner ORDER BY Clause and external ROWNUM condition..If I eliminate any of the two, the query works fine...I am not sure if indexes have some role to play in it...
View 1 Replies
View Related
Jan 25, 2013
I'm on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
I am working on a project where have lots of view on a different schema. For performance reasons, we create tables on those views and index them.
The application that uses these tables requires a numeric primary key of a specific length, e.g. number(10). Not all tables have a natural key that matches this requirement, so I added a rownum to the query. I had hoped that casting the rownum to a number(10) would result in the same datatype once the table is created.
e.g.
SQL> create or replace view rownum_to_number10_vw as
2 select cast(rownum as number(10)) objectid, dummy from dual;
View created
SQL> describe rownum_to_number10_vw;
Name Type Nullable Default Comments
-------- ---------------- -------- ------- --------
OBJECTID NUMBER(10) Y
DUMMY VARCHAR2(1 BYTE) Y
SQL> perfect! Now create a table based on this view:
SQL> create table rownum_to_number10_tb as 2 select * from rownum_to_number10_vw;
Table created
SQL> describe rownum_to_number10_tb;
Name Type Nullable Default Comments
-------- ---------------- -------- ------- --------
OBJECTID NUMBER Y
DUMMY VARCHAR2(1 BYTE) Y Oracle does not pick up on the number(10) cast!
How can I force Oracle to create a column with the same datetype as the underlying query?
ps:I know that the 10 in number(10) is more like a constraint than a datatype, but the application that uses this table will create an additional column if the datatype > 10. I want to prevent that from happening...
View 8 Replies
View Related
Feb 18, 2013
I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2
Will the above query solve my purpose?
View 11 Replies
View Related
Nov 27, 2012
I am using this as a subquery within a large select statement.
(select NAME_LAST from person_name where person_id=enc.person_id and ROWNUM = 1 order by person_name_id desc) as PatFirstName
I am getting issues when i am doing rownum=1 with order by clause, what is teh right way.
when i use rownum < 2 without order y clause it is workign fine.
I would like to use order by clause.
View 2 Replies
View Related
Jun 1, 2010
I have a result set with three columns as 'Product Category', 'Product' & 'QtySales' and 10 rows, sorted in the order Product Category, Product. This means, a product category will have one or more products under it.
Now i want to add a fourth column to my result set, which should display a incremental number sequence from starting from 1, 2, 3.. for each row. Also when the value of the Product Category (1st column) changes, this sequence should be restarted again from 1.
Col1 Col2 Col3 Col4
PC1 P1 10 1
PC1 P2 20 2
PC2 P3 30 1
PC2 P4 10 2
PC2 P5 15 3
PC3 P6 25 1
View 1 Replies
View Related
May 24, 2011
I have a query regarding the use of rownum inside the insert statement.
For example, I have a sample table as: sample1(aa date, bb number);
Insert
INTO sample1
VALUES (SYSDATE, ROWNUM);
this statement is working fine in Oracle 9i but gives error in Oracle 11.2.0.1. The error is ORA-976 ,
Why this error coming in Oracle 11g and how to resolve it?
Our Environment: UNIX AIX 5.3, Oracle 11.2.0.1 database
View 1 Replies
View Related
Mar 17, 2013
can we create rank on a particular column without using rownum and rank function.
View 9 Replies
View Related
Nov 27, 2010
Using rownum in PL/SQL can significantly reduce performance and throughput of queries.
For example,
CODEselect *
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...)
where rownum < 500
takes much more time on a heavy loaded db than
CODEselect Y.*
from (
select X.*, rank() over(order by ...) rnk
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...) X) Y
where rnk < 500
I suspect it's because Oracle optimizer goals all_rows and first_rows.
View 2 Replies
View Related
Dec 9, 2011
I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time
INTO on_time2
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date
WHERE ROWNUM = 2;
--RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time
INTO on_time2
FROM (SELECT on_time
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date)
WHERE ROWNUM = 2;
I can't move forward in my form until I figure out why this is not returning records
View 4 Replies
View Related
Dec 27, 2010
high number of executions of specific types of queries which is using only rownum clause. For exam.
select ani, rowid from tbl_smschat_upuor where rownum<=:"SYS_B_0";
DB is having high number of executions of these type of queries and these when I m checking the execution plan for the same type of queries it is accessing the full table scan.
======================execution plan for above query
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 91289622
--------------------------------------------------------------------------------
[code]....
View 3 Replies
View Related
Jan 29, 2013
bookshelf_test table structure
title varchar2(100) y
publisher varchar2(20) y
categoryname varchar2(20) y
rating varchar2(2) y
my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS
2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS
3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON
2 5 THE MISMEASURE OF MAN W.W. NORTON
3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS
4 5 MY LEDGER KOCH PRESS
5 4 TRUMAN SIMON SCHUSTER
6 4 GOSPEL PICADOR
7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC
8 4 INNUMERACY VINTAGE BOOKS
9 4 JOHN ADAMS SIMON SCHUSTER
10 4 JOURNALS OF LEWIS AND CLARK MARINER
11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER
12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS
13 4 THE SHIPPING NEWS SIMON SCHUSTER
14 4 THE GOOD BOOK BARD
15 4 THE DISCOVERERS RANDOM HOUSE
16 3 THE COST OF DISCIPLESHIP TOUCHSTONE
17 3 SHOELESS JOE MARINER
18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR
19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS
20 3 EITHER/OR PENGUIN
21 3 CHARLOTTE'S WEB HARPERTROPHY
22 3 BOX SOCIALS MARINER
23 3 ANNE OF GREEN GABLES GRAMMERCY
24 3 WEST WITH THE NIGHT NORTH POINT PRESS
25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB
26 3 TRUMPET OF THE SWAN HARPERCOLLINS
27 2 COMPLETE POEMS OF JOHN KEATS VIKING
28 1 POLAR EXPRESS HOUGHTON MIFFLIN
29 1 GOOD DOG, CARL LITTLE SIMON
30 1 MIDNIGHT MAGIC SCHOLASTIC
31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
View 3 Replies
View Related
Apr 23, 2010
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87
if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.
View 3 Replies
View Related
Jun 23, 2011
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
View 16 Replies
View Related
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
