SQL & PL/SQL :: No Rows Selected Error?

Jun 23, 2013

I have homework due, and I'm so stuck: This worked when I first ran it, and now all 6 queries return the same error No Rows selected. I did have to add a column to a table, and insert data for that column before this worked.

> --------------------------
> --REPORT 1
> ---------------------------

SELECT sur.channo,
supp.supplierid,
sur.packtype,
program_name,
ratingcode,

[code]....

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Selected Number Of Rows From A Table

Aug 3, 2011

Can we select table's rows of 5 to 15, like

select * from emp
where rowid <=5;

It is through an error when i used the below method.

select * from emp
where rowid >=5 and rowid <=15;

View 4 Replies View Related

SQL & PL/SQL :: Date Format - No Rows Selected

Jun 18, 2013

I am trying to run the following queries in my database.

Q-1)select * from item where trunc(approval_date)='21-MAY-13'
When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database.
Q-2)select * from item where approval_date=to_date('21-MAY-13')
When Q-2 is executed it says no rows selected.

difference or if any links related to this.

View 4 Replies View Related

SQL & PL/SQL :: Handling No Of Rows Selected In Loops?

Feb 25, 2012

I'm attempting to write a plsql for finding missing archived logs for streams.

requirement is to run a select statement and print
1. 'NOT FOUND' if name column is null
2. '<name of the files>' if rows are returned
3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)

code i developed so for:
for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES'
and status!='A')
loop
if (cr.name = 'NOT FOUND')

[code]...

View 6 Replies View Related

Apply Referential Integrity To Only Selected Rows Of Particular Column?

Mar 23, 2011

Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.

no, and it makes no sense to do so either

We have an appointment form in our HIS, where patients take telephonic appointment. That time they may not know their user id given by hospital. So, it remains blank & name is entered manually. But if the user-id is entered, it must fetch patient name from the master.

The candidate key must be unique within its domain.The candidate key can not hold NULL values.

ALTER TABLE HLTHCHKAPPOINTHD ADD (
CONSTRAINT SYS_C007145
FOREIGN KEY (N_PATIENTMR_ID)
REFERENCES MRREGISTRTNHD (N_PATIENTMR_ID));

MRREGISTRTNHD is a patient master & has a primary key named N_PATIENTMR_ID.
HLTHCHKAPPOINTHD is a appointment table & has a foreign key named N_PATIENTMR_ID which references N_PATIENTMR_ID of MRREGISTRTNHD.

Also, N_PATIENTMR_ID of HLTHCHKAPPOINTHD is not a unique key & it can contain null values also. I want to define constraint or any other method such that only the not null values are referenced to the master i.e. it should validate in the master. And null values should skip this reference.

Now, what happens is due to this constraint, when I'm trying to edit & update the rows having null value in N_PATIENTMR_ID, it gives the following error.

ORA-02291: Integrity constraint (SYS_C007145) violated - parent key not found

So, can I give some condition in the above constraint saying, apply this constraint to table HLTHCHKAPPOINTHD only having the not-null values in N_PATIENTMR_ID coulmn?

View 2 Replies View Related

SQL & PL/SQL :: All Selected N Consecutive Rows Retrieved In Single Query

Nov 11, 2011

I have table T with 50,000 rows

create table T
(student_id number,
class_id number,
quiz_id number,
marks number)

some sample rows like

INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, ‘29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, ‘34);
INSERT INTO T VALUES (2,2, 9, 11);

each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id

I am doing the below analysis and query:

1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..

SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)

[Code]....

SQL> /

QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11

15 rows selected.

With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..

since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...

my sample output is:

Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?

Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.

View 7 Replies View Related

Client Tools :: (X Rows Selected) Message Being Suppressed?

Jun 17, 2010

Am using the SQL*PLus tool via a command line to execute some simple "select" scripts. Scripts work fine in what they produce - except for something that is missing occasionally.

For SOME scripts, I'm getting no "xx Rows Selected" type messages at the bottom and I can't figure out why. When no data si returned in some (different) scripts I do get a "no rows selected" message. Am looking for this message in all outputs as an "end of report"-style marker.

No complicated SQL logic and I have the following standard settings applied into each script.

set linesize 500
set pagesize 50000
set tab off
set wrap off
set colsep '|'

I have 20 years of coding experience so it's not a basic error....albeit I'm far more familiar with MS SQL Server product set as well as the PL/SQL Developer client front ends for Oracle.

View 6 Replies View Related

Application Express :: Interactive Report  Count Selected Rows?

Jul 6, 2012

I have a I/R report with a checkbox is column 1 of each row using:

HTMLDB_ITEM.CHECKBOX
(1,R.RESEARCH_ID,
decode(:Pxx_SELECT_ALL_ROWS,'Y','CHECKED',
decode(instr(nvl(:Pxx_SELECTED_IDS,0),R.RESEARCH_ID),0,NULL,'CHECKED'))
)
AS select_flag

I want to use Javscript or a Dynamic Action to "count" how many selected rows there are, when a UPDATE button is pressed. Right now, the UPDATE button "submits" the page, then a page item computation runs the following code to get a count:

DECLARE
v_cnt number(9);
BEGIN
v_cnt := 0;
FOR i in 1..htmldb_application.g_f01.count
LOOP
v_cnt := v_cnt + 1;
END LOOP;
return v_cnt;

This works... but I dont want to submit (and rerun the report) just to get this count.

I have copied this loop into a Dynamic Action using the Click action of the UPDATE button, but it doesnt seem to recognize htmldb_application.g_f01.count .. so my value returns as 0.

View 7 Replies View Related

Duplicating Multiple Rows - Getting Error?

Sep 9, 2008

I'm getting an error "SQL command not properly ended" with the following complex delete duplicates query.

DELETE FROM A_EMIS_TOXIC_DTL
WHERE exists
(
select dcf.MASTER_AI_ID,
dcf.INT_DOC_ID,
txd.SUBJECT_ITEM_CATEGORY_CODE,

[code]...

View 2 Replies View Related

Forms :: WUT-113 - Too Many Rows Error Using Webutil To Upload Doc Or PDF

Jun 1, 2010

I'm new to using Webutil to upload and view files and am receiving the error msg 'WUT-113 Too many rows matched the supplied where clause' The process works fine for uploading photos but not when I try and upload documents and pdf files.

The code example (for documents) is outlined below but think that the issue must be to do with some sort of incorrect Webutil configuration on my machine.

declare
vfilename varchar2(3000);
vboolean boolean;

[Code]...

The :blob_id column is populated on the form with the when-create-record trigger. Why is the program at time of upload to the db saying that more than 1 row is being written to the db table?

View 4 Replies View Related

RMAN Error Maximum Number Of 128 Rows Exceeded

Nov 2, 2010

I read the error description. In my case there is only one full backup at a time running, hence it does not make any sense.

RMAN>
connected to target database: OTATEST (DBID=3130218754)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded

SQL> select count(*) from V$RMAN_STATUS;

COUNT(*)
----------
560

SQL> select count(*) from V$RMAN_OUTPUT;

COUNT(*)
----------
32834

View 1 Replies View Related

SQL & PL/SQL :: Why NO Row Selected Against Last Query

Apr 21, 2013

There is a simple table prod having a column crlf.i did the following

Quote:
SQL> update prod set crlf=null;

716 rows updated.

SQL> commit;

Commit complete.

SQL> select * from prod where crlf=null;

no rows selected

why NO row selected against last query?

View 3 Replies View Related

SQL & PL/SQL :: How To Truncate For Selected Tables

Jun 8, 2012

i have many user tables in my database and i want to truncate selected tables and how i can overcome this scenario.

i can truncate using truncate table <table_name> but they are 250 tables to truncate, so,i can't write truncate command for every table. if i want to truncate first i have to truncate parent table or child table and how i can find parent table and child table for given tables to truncatein my database.

View 6 Replies View Related

PL/SQL :: How To Leapfrog Selected Record And Go To Next One

Jul 1, 2013

DB 11G , OS Linux Redhat , i have a Linux script to read the name of the files listed on the Table , then open those files and save the content on another table , i need to speed up the process by running this script multiple time , so i need toread the first record (File name ) then running the script again to check if the record already selected then go to the second record and so on . is there any way to check the selected row then leapfrog this record  and going to the next record . Im already tried (for update skip locked ) but this will  return ( no row selected message when locked ) and  when running on the 2 Scripts  it will considering it as same session and always will be the same . my query is (select bi_file_namefrom dbm_cdr_headwhere bi_file_name like 'hd_auto%' and bi_auto_status=35;) 

View 3 Replies View Related

SQL & PL/SQL :: Row Count In Selected Materialized Views

Nov 26, 2010

I have two problems.

1.I have the following query--

select name from user_snapshots where type = 'COMPLETE';

I can't do anything except running 'select' statement. NO PL/SQL as well.

how to wrote a query that counts the number of rows in individual materialized views selected by the above query.

2. How to store the result of a select statement into a variable using SQL (not PL/SQL)

select count(*) from tab;

now I want to store count of tables into a variable. Is it possible?

View 4 Replies View Related

Forms :: Deselect All Checkboxes Except The One Which Has Been Selected

Jun 30, 2009

I have multi record block and a select checkbox in that block. My requirement is whenever I select any checkbox all other checkboxes should get deselected except the one which I have selected just now.

Example: I started from first item (checkbox).

I select first checkbox and when I will select second checkbox, then 2nd should get checked but all other checkboxes should get unchecked.

View 8 Replies View Related

Server Utilities :: Exp / Imp Of Selected View

Sep 22, 2010

i need to exp/import select ted tables and views

i know how to do exp/imp tables, but how to do views

My product 10g/linux

View 9 Replies View Related

Unable To Access The Selected Application

Oct 9, 2012

I trying to deploy contract management in the weblogic server 12c . using the ear file in the following location

C:Program FilesOracleContractManagement_1earexpedition.ear

When i click on next I get the series of error. Really I don't understand this What are these errors,

Unable to access the selected application.
Exception in AppMerge flows' progression
Exception in AppMerge flows' progression
Unable to resolve deadlock in factory claims
Unable to resolve deadlock in factory claims

View 2 Replies View Related

SQL & PL/SQL :: Max Date Using Selected Columns - Millions Of Records

Dec 16, 2011

I am having a requirement like below,

Scene 1:

If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.

Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013

Output needed:

SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014

Scene 2:

If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.

Table Structure:

SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013

Output needed:

SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013

How to achieve this requirement. My source data has 25 million of records like this.

View 2 Replies View Related

SQL & PL/SQL :: ORA-22992 - Cannot USE LOB Locator Selected FROM Remote TABLES?

Feb 10, 2012

I have facing the below problem while the executing the query,

SELECT tmp1. *
FROM
tmp1
LEFT OUTER JOIN tmp2 ON tmp1.NUM_MAIN_ID = tmp2.NUM_MAIN_ID;
ORA-22992: cannot USE LOB locators selected FROM remote TABLES
Tmp1 TABLE as CLOB field.
Tmp1 is remote TABLE, for which i have given dblink

View 3 Replies View Related

Forms :: Copy Selected Records To Another Block?

Mar 6, 2012

I have 2 blocks, namely addsubject3rd and chosensubjects. I'm copying the selected records(this comes with checkboxes) from the 1st block to the second block. But what happened is that it copied all the records that are on the addsubject3rd. How can I copy only selected records(checkboxes) from addsubject3rd block to chosensubjects blocks?

View 5 Replies View Related

Forms :: Adding Selected List Box Value Into Text Box

Oct 29, 2013

I am creating one application in oracle forms in which one push button is dere for dept_list and textbox for displaying dept_name, after clicking button it shows list of dept_id and dept_name in listbox. and after selecting any one value from it adding into textbox of dept_name and update it. How to do this in oracle forms 6i?

View 2 Replies View Related

Forms :: Binding Selected Data From One Datablock To Another

Mar 3, 2012

I've got a problem with binding my selected data from one data block to another. When I'd finished selecting my data(subjects in this case),

and I clicked schedule button, my selected data were not bound into the textboxes in schedule datablock.this what happened.the code i used in when-checkbox-changed trigger:

BEGIN
IF :NEWSTUDENTS.cb_subj = 'N' THEN
Clear_record;
ELSIF :NEWSTUDENTS.cb_subj = 'Y' THEN
:SCHEDULE2.Subject := :newstudents.subject_code;
END IF;
END;

and in tree-node-activated trigger:

MEssage('FOR LOOP - subj_code = ' || subj_rec.subject_code);
:NEWSTUDENTS.subject_code := label_of_mynode;
:NEWSTUDENTS.subject_= subj_rec.subject_code;
:NEWSTUDENTS.units:= subj_rec.units;
MESSAGE('POSITION OF CURSOR ' || :SYSTEM.CURSOR_RECORD);
[code]....

View 30 Replies View Related

PL/SQL :: Updating A Column Randomly With Selected Values?

Sep 26, 2013

 We've an accounts table that basically represents hotel chains & brands. an example shown below. 

account_id chain_id brand_id  service1              
NULL     NULL          111              
NULL     NULL          122              
NULL     NULL           11

 Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.

There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,

chain_id  brand_id         
101     2011          101    
2012          102     2020 

Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg., 

account_id chain_id brand_id service1              
101          2011     111               101         
2011     122               102          2020     11 

so each account can be attached to only one chain_id and one brand_id.

View 2 Replies View Related

Index Usage Depends On Columns Selected

Mar 6, 2013

somehow cannot understand why index is not used

in Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

1.     Included only indexed column and got a perfect plan

explain plan for2 select s.x_cnt
3 from reported_summary s
4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2674489506

-------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 306 (8)|
|* 1 | INDEX FAST FULL SCAN| S_NUI01 | 306 (8)|
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

2.     Included some other column and got TABLE ACCESS FULL

explain plan for2 select s.x_cnt,s.ru_id
3 from reported_summary s
4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335

---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

3.     Included all other columns and got TABLE ACCESS FULL as well explain plan for2 select s.x_cnt,s.* 3 from reported_summary s 4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335

---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

View 5 Replies View Related

Pull All Degrees Into Table Based On Which Institution Is Selected?

Mar 26, 2013

I'm trying to pull all the degrees into a table based on which institution is selected. If institution is 'AAA' or 'BBB' then pull ACAD_PLAN, DESCR by ACAD_PROG where ACAD_PROG >= some value and <= some other value.

If institution is 'CCC' then pull ACAD_PLAN, DESCR by institution regardless of ACAD_PROG.

Something like

INSERT INTO table
SELECT
'value_a'

[Code].....

I don't have this formatted right cause it keep telling me missing keywords.

View 1 Replies View Related

Forms :: Verification That User Selected Valid Date

Mar 19, 2013

I have 3 types list items:

1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013

I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected

View 9 Replies View Related

SQL & PL/SQL :: Calculations - Get Same Month From Previous Year Of Selected Date

Sep 20, 2011

i have a requirement to create a query which gets the same month from the previous year of the selected date .

Example : i select SEP-2011 ( as sysdate )

it will return the SEP-2010 as well .

here is the code which works fine with sysdate, dual :

SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;

in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)

am unable to apply the above code on it , i guess its the data type pf the field .

View 5 Replies View Related

Forms :: How To Populate City Based On Selected State

May 14, 2010

I'am having a query regarding population of a list item based on another selected list item that is whenever i select the state from the combo box all the city's under that state should be automatically populated.

View 4 Replies View Related

Backup & Recovery :: Selected Data From Tables Of Schema

Mar 7, 2012

I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.

View 11 Replies View Related







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