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?
I have an query i.e.
I want 3 lines input in 1 line using 2 union operator like
Input:-
'i love playing
football and
volleyball'
i want the output like:-
"i love playing football and volleyball"
solve query using 2 union operator?
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.
Query1:
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept
Query2:
SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)
I mean to say query1 is better (or) query2 is better,which query improves the performance.
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!!
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?
In my sql query, how can i fetch the row with max row count? the query has around 10 columns.
View 2 Replies View RelatedWhen 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...
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...
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.
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
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
can we create rank on a particular column without using rownum and rank function.
View 9 Replies View RelatedUsing 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.
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
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]....
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 ?
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.
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
create table my_tab
(U_name varchar2(30) ,
constraint ck_check (U_name = upper(m_name))
/
Any alternate to the following query.
select * from emp where ename like upper(NVL('%mi%',ename));
Basically I want to search based on string or null.
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible
i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .
for example ...
if search string=string2 then
message('Record found');
end if ;
i want to use like '%search string%'.
1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?
Employee name ----> rama_krishna_raj
what the difference between IN and EXISTS operator. Why should we use EXISTS operator?
View 1 Replies View RelatedI have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".
select q'[Oracle's world ]'
from dual
But The following query works.
select q'[It's Oracle's world ]'
from dual
Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB )
Number of downloads: 1
view_def.txt ( 14.12K )
Number of downloads: 2
The following runs no problem
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND
[code]....
However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
[code]....
I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.
How to replace the like operator for increase the performance. Because it is taking more time and not using the index.
SELECT *
FROM emp
WHERE ename like '%AL';
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual;
--Output H S
Select Concat('H ',' S') A From Dual;
--Output H S
Select Length('H '||' S') A From Dual;
--Output 6
Select Length(Concat('H ',' S')) A From Dual;
--Output 6