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


ADVERTISEMENT

Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows

Aug 18, 2010

ORA-01422: exact fetch returns more than requested number of rows.

I receive this error because i tried to introduce the below coding in a post query of the block.

begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

SQL & PL/SQL :: Why Greatest Returns Null As Greatest Value While Max Returns 1

Aug 4, 2010

SQL> select greatest ( 1000,null) from dual;

GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;

MAX(A)
----------
1

Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?

View 17 Replies View Related

SQL & PL/SQL :: Join Running Forever - Returns No Rows

May 23, 2012

select rl.org_rollup_skey from (select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486')) p
INNER JOIN IA.DIM_ORG_HIER h
ON p.fc_org_skey = h.desc_org_skey
inner join IA.FCT_FCST_SLS_ORG_ROLLUP rl
on h.GPRNT_ORG_SKEY = rl.org_rollup_skey

Above join is taking is running forever even as subquery

(select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486'))

returns no rows and this subquery give result in 10 seconds according to me Full query should not take more tha 20 secs.

View 1 Replies View Related

SQL & PL/SQL :: Query Returns Multiple Rows / How To Get Distinct Row

Jul 18, 2010

in 10g report builder i have written this query

----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP

[code]....

this query returns multiple row. how to get distinct row.

View 5 Replies View Related

Performance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time

Feb 11, 2011

Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.

Original query and plan

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....

Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.

View 6 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 :: Rows With Column Value Always NULL

May 31, 2011

I have a table with columns emp_i, LOC_C and SUBSID_C. I want to find all emp_i's with LOC_C OR SUBSID_C as always NULL. Please note that the value should be NULL, always, for all dates.

--------------------------------------------
EX:
emp_i----LOC_C---SUBSID_C--Eff_Date
100------20------30--------01/01/2011
101------NULL----10--------05/01/2011
102------NULL----NULL------02/01/2011
101------20------NULL------02/01/2011
102------NULL----NULL------05/01/2011
103------NULL----20--------01/01/2011
103------NULL----NULL------02/01/2011
--------------------------------------------

The query ---
should return 102 as LOC_C OR SUBSID_C is ALWAYS NULL.
should return 103 as LOC_C is ALWAYS NULL.
should not return 101, as LOC_C is not ALWAYS NULL.
In other words, the query should give list of emp_i who never ever had a non-null value for LOC_C OR SUBSID_C. The purpose is to find the emp_i for which the columns LOC_C and SUBSID_C are never used.

I tried the query:
---------------------------------------------------
SELECT DISTINCT ORG_EMP_I FROM tab1
WHERE ORG_GRP_I = 58
AND ORG_EMP_I NOT IN (
SELECT DISTINCT org_emp_i
FROM tab1 ap
WHERE ap.ORG_GRP_I = 58
AND trim(ap.LOC_C) IS NOT NULL OR ap.ORG_SBSID_C IS NOT NULL )
---------------------------------------------------

View 10 Replies View Related

SQL & PL/SQL :: To Display Null Rows When No Match

Apr 23, 2010

I have table as follows:

create table sample1 (
i number ,
j date,
k number)

insert into sample1 values (1,'23-Apr-2010',11)
insert into sample1 values (2,'22-Apr-2010',12)
insert into sample1 values (3,'21-Apr-2010',13)
insert into sample1 values (4,'19-Apr-2010',14)
insert into sample1 values (5,'18-Apr-2010',15)
insert into sample1 values (6,'17-Apr-2010',16)

I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.

I did it through "UNIX" , but it's not efficient.

The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:

select i, j , sum(k)
from sample1
where j in (select to_date(sysdate - rownum)
from dual
connect by rownum < = 7)
group by i, j

View 3 Replies View Related

PL/SQL :: How To Eliminate Null Rows From The Result Set

Jul 10, 2012

My requirement is as follows .

Oracle version details
BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE     11.1.0.7.0     Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production

[code]....

View 6 Replies View Related

List Count Of Rows Where DATE Field Is Not Null?

Apr 1, 2010

I need to list a count of rows where a DATE field is not null and group those counts by day.

Here's my sql so far...

SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;

Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?

View 2 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

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 :: 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 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

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 :: Use Greater And Less Than In Left Join?

Apr 9, 2013

I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).

The create table script for the lookup is like this:

CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)

Sample inserts are:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.

The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from

[code]...

As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.

My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.

What I mean is (with only one time value and not lbound and ubound:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);

View 4 Replies View Related

SQL & PL/SQL :: Precision Greater Than Actual Value

Apr 6, 2012

When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value

SQL> drop table precision_test;
Table dropped
SQL> create table precision_test(name number(2,5));
Table created
SQL> insert into precision_test values (1);
insert into precision_test values (1)
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Records Greater Than Row Number?

Mar 9, 2011

I have two sql queries. They run the one after another.

Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc

Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc

Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?

View 7 Replies View Related

SQL & PL/SQL :: Time Comparison Alone Like Less / Greater Than?

Dec 15, 2010

Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >

[code]...

from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?

View 3 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

I am running a GROUP BY query on a few columns of enumerated data like:

select count(*), Condition, Size
group by Condition, Size;

COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S

Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.

For example:

COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL

View 9 Replies View Related

SQL & PL/SQL :: Date Field - Not Null Column To NULL

Mar 16, 2011

I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.

I am using below query.

ALTER TABLE T_test
modify (paid_to_date null)

View 9 Replies View Related

Forms :: Null Value Not Passing

Oct 18, 2011

I am trying to pass null value '' in form but still failed

---SQL---
select distinct column1 from abc
Y
NULL
two records found.

---FORM---
I have a list item name user_pick which is in two values SOUND-Y, DAMAGE-'' passing null value

--BUTTON--
select count(column1) into A from abc
where column1=:user_pick;

when user pick from list SOUND it is ok..When user pick damage this will show 0 means null value are not passing correctly.i also tried in radio group,check box

View 4 Replies View Related

Forms :: 6i - REPORT_OBJECT_STATUS Cannot Be Null

Jan 21, 2010

I have an existing form and report. We recreated our testing environment and now when running any report I first get the following error: REP-3002: Error initializing printer. make sure a printer in installed.

This error pops up when calling RUN_REPORT_OBJECT.

The next line of code checks the report status by using REPORT_OBJECT_STATUS. This check throws out error: FRM-40738: Argument1 to builtin REPORT_OBJECT_STATUS cannot be null.

This indicates that my FIND_REPORT_OBJECT is not returning a value.

All these reports and forms used to work fine on our old testing environment and also works on our live database.

View 4 Replies View Related







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