PL/SQL :: Cast Rownum To Number (10)

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


ADVERTISEMENT

SQL & PL/SQL :: Number Sequence At Row Level Like ROWNUM

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

SQL & PL/SQL :: Table Type Cast

May 6, 2010

FOR n
IN (SELECT invtry_alloc_id,
invtry_item_id,
oh1_alloc,
oo1_alloc,

[Code].....

is giving me error "invalid datatype" ora-00902 what could be the reason

create or replace type invtry_alloc_obj is object
(invtry_alloc_id number,
invtry_item_id number,
oh1_alloc number,

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: User Defined Data Types In Cast Functions

Jun 22, 2010

I am migrating sybase to oracle database. A Java developer needs money datatypes.I said to them, please change the cast(<value> as number(19,4) in java code side. but they are not accepted because money data type is used most of the places.

select cast(0 as money) from bank_trans; this sql statements present in java code. I need to create user defined type is equivalent to money datatype.

My steps

I have create user defined data types

create or replace type money as object(money as numbeer(19,4)

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

create or replace type money is table of numbeer(19,4);

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

View 10 Replies View Related

Using Both OrderBy And RowNum In Query

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

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 View Related

PL/SQL :: How To Fetch A Row With Maximum Rownum

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

SQL & PL/SQL :: ORDER BY Clause And ROWNUM

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

PL/SQL :: Rownum In UNION Operator

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

SQL & PL/SQL :: Select Query With Rownum

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

Using Of ROWNUM In Insert Statement Gives Error In Oracle 11g

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

SQL & PL/SQL :: Create Rank On Column Without Using Rownum Function

Mar 17, 2013

can we create rank on a particular column without using rownum and rank function.

View 9 Replies View Related

Using Rownum In PL/SQL Can Significantly Reduce Performance And Throughput Of Queries

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

Forms :: ROWNUM Returns NULL Rows When Value Greater Than 1

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

Performance Tuning :: How To Overcome Rownum Clause From Select

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

PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2

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

Forms :: How To Select 1st Record From The Duplicate Values In Table Without Using Rownum And Rowid

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

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

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

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Replacing 4 Digit Number In A Given String With The Same Number Incremented By 10000?

Jun 17, 2010

i want to replace 4 digit number in a given string with the same number incremented by 10000.

That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).

Input String:

<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

Required output :

<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>

View 7 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

Express Edition (XE) :: Why Maximum Number Of Voting Disk Is Even Number (32)

Oct 3, 2013

I gone through many forums and found that the number of voting disks should be always in odd number. Then why the maximum number of voting disk is 32?

View 1 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

SQL & PL/SQL :: Number Generation MINUS Number Used

Feb 19, 2011

Here is script of tables

Quote:drop table p;
create table p (qty number(3), beg_no number(5));
insert into p values(5, 110);
insert into p values(8, 786);

drop table s;

create table s (used_no number(5));
insert into s values(111);
insert into s values(113);
insert into s values(791);

Table p: it has ticket quantity and ticket begining number. Thus according to first record ticket number will begin at 110 and will end at 110+5 (Beg_no +qty). According to second record ticket number will begin at 786 and will end at 786+8 (Beg_no +qty). This table can have many records.

Table s: it has ticket numbers which are sold. The ticket will always be any number from table and will lay in any record in this format between beg_no and beg_no+qty

Required: I want "p MINUS s" information. i.e.

Quote:
110
112
114
786
787
788
789
790
792
793

I am not expert in level by command.

Oracle version: 9
OS: Windows XP

View 3 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

SQL & PL/SQL :: Add Number Containing (,)?

Apr 19, 2010

i have field(column name ) amata type is number . in excel i am having "," in amount field eg 9,999. how to do it

View 6 Replies View Related

SQL & PL/SQL :: How To Get Serial Number

Mar 12, 2013

I have following table.

CREATE TABLE ABC
(
DPT_NUM NUMBER,
LOT_NUM NUMBER,
ASSOCIATED_WITH_LOT NUMBER
);
SET DEFINE OFF;
Insert into ABC
[code]......

Now i run follwoing query and result is

select * from abc
DPT_NUMLOT_NUMASSOCIATED_WITH_LOT

1501
1502501
1509501
1511
1503
1516
1522
1565
1569565
2601
2602
2604602
2607
2508

I need following result

DPT_NUMLOT_NUMASSOCIATED_WITH_LOT Serial_Number

1501 1
1502501 2
1509501 2
1511 3
1503 4
1516 1
1522 2
1565 3
1569565 3
2601 1
2602 2
2604602 2
2607 3
2508 3

Note that serial number must reset after each 4 lot_num againt dpt_num. Not that where lot is associated in associated_with_lot there serial number must be same as serial number is for associated lot.

View 7 Replies View Related

SQL & PL/SQL :: How To Convert RAW To Number

Sep 27, 2013

can we convert RAW to number in sql.because i have to bitand of one raw and number variable.

View 3 Replies View Related







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