SQL & PL/SQL :: Set Of Data After Every X Number Of Records

Feb 24, 2012

I have the following requirement.

Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted

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

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 :: Read Different Number Of Records

Jan 30, 2013

I know how to use cursors to read all the records in a table.

But how can records that should be processed together be read in a loop, using cursors?

If there were, for instance persons from different nationalities, French, German, English, ..., I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.

View 34 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

PL/SQL :: DB Parameter To Process More Number Of Records

Oct 18, 2012

We have a package that processes(Insert and Update) set of records and runs in loop.

Package is working fine when we set number of records to 300 for each loop and commit. when we increase the number of records to 1000, package is getting stuck(Not ending and not coming out of loop).

Is there any DB parameter to be changed in my environment to process more number of records at at time?

View 6 Replies View Related

Forms :: Number Of Records Retrieved In Block

Nov 30, 2010

is there any way other than this way to retrieve the number of record displayed in the block ?

last record ;
l := :system.cursor_record

View 1 Replies View Related

Forms :: Displaying Number Of Records With Condition

Jul 8, 2012

In form i call a record from database table through execute_query. i have lov on ITEM_NO

i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.

if i press the down arrow it should show next record of the same item_no.

View 5 Replies View Related

SQL & PL/SQL :: Difference In Number Of Records In GROUP BY And PARTITION BY

Feb 17, 2012

If I run the following query I got 997 records by using GROUP BY.

SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;

I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.

SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....

why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.

View 8 Replies View Related

Reports & Discoverer :: Number Of Records On Run Time?

Oct 16, 2012

MY problem is in my reports i wish to have filter number of records on my requirement , when i call reports from form then ask me that how many number of records you want..

View 3 Replies View Related

SQL & PL/SQL :: Inserting Number Of Records Loaded Into Log Table

Mar 8, 2013

I got Scenario that, i need to insert the number of records loaded in target table into the log table.

But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.

/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......

View 7 Replies View Related

SQL & PL/SQL :: Update Column With Total Number Of Records

Apr 3, 2012

Table : Customer

Cust_idNameid
227ABC1
227ABC2
227ABC3
228XYZ1
228XYZ2
228XYZ3
228XYZ4

In one oracle table (Customer) we have multiple cust_id based on that, we want to populate id column as cust_id record count.

I have id column right now null and want result same as above.

View 1 Replies View Related

SQL & PL/SQL :: How To Create Procedure For Getting Number Of Records Of A Table

Jul 6, 2011

How to create procedure for getting number of records of a table

View 24 Replies View Related

SQL & PL/SQL :: To Verify Huge Number Of Records In Two Different Databases

Jun 5, 2012

I need to verify huge number of records in two different databases. Basically i wanted to check if same record exist in other database's table or not? but as the number of records are more than billions who would i verify?

checking record one by one would be so hectic and time consuming. any other option is there?

View 11 Replies View Related

Combine Tables With Union But Limit Number Of Records?

Sep 13, 2007

Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.

I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?

The query is:

SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

Forms :: Showing Number Of Records Affected (saved)

Jul 31, 2010

We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)

Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"

We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)

Under which particular trigger we should write the code to enhance this requirement?

View 2 Replies View Related

Forms :: Number Of Records Displayed In List Item Is More Than 1

Dec 28, 2010

Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.

View 12 Replies View Related

Forms :: Dynamically Change Number Of Records Displayed?

Mar 31, 2011

I am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.

View 2 Replies View Related

SQL & PL/SQL :: Export Large Number Of Records From Select Into Text File

Feb 9, 2011

I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?

DECLARE
fileid UTL_FILE.file_type;
BEGIN
fileid := UTL_FILE.fopen ('VRS_CEM', 'cust.txt', 'W');
UTL_FILE.put ( fileid, 'IMSI|MSISDN|CONTRACT');
UTL_FILE.NEW_LINE(fileid);
[code]....

View 8 Replies View Related

Forms :: How To Show Total Number Of Records In Text Item

May 3, 2013

I have a multi record field of five rows. And 3 values.

the values are

IT
CSE
ECE

And i have one text item name is COUNT.

how to show the total number of records in Text item .

View 10 Replies View Related

Forms :: Limit Number Of Detail Records For Every Parent Record To 8?

Nov 10, 2010

1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?

2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?

3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.

View 2 Replies View Related

Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field

Jul 13, 2011

I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.

How to set any triggers for the items to auto populate and i am suppose to finish this work today.

View 2 Replies View Related

SQL & PL/SQL :: Query To Fill Data From Top Records

Mar 5, 2010

I want to fill data from the above records in the same column. How to do that by use SQL command ?

from
Invoice_No.Line_Item
000011
2
3
000021
2
000031
000041
2
000051

to
Invoice_No.Line_Item
000011
000012
000013
000021
000022
000031
000041
000042
000051

View 10 Replies View Related

SQL & PL/SQL :: How To Display Data Without Duplicate Records

Nov 16, 2011

how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.

select distinct comp_id as compid,
comp_disp_name as company,
plcy_id as policyid,

[Code]....

output
-----------------
compid compname policy_id policy_name

19734 Save the Children 9013 GPA
19734 Save the Children 9012 GMC
20097 JMT 9486 GTL
10890 Steelco Gujarat Ltd. 9727 CAR
17330 Golden Jubilee Hotels Limited 8915 CGL
23117 NBHC 9093 GMC
17542 Heinz India 10693 Fire
19821 KSK Fabricators 10341 D&O
3769 Jones Lang Lasalle India 9199 WC
19821 KSK Fabricators 10340 WC

View 10 Replies View Related

SQL & PL/SQL :: Collection Data - Get All Records From Table?

Nov 5, 2012

CREATE TABLE table_a(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_b(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_c(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
INSERT INTO table_c VALUES(3,100,100);

[code]...

for each of the is which I get in v_ids, if there are records in a,b,c then I should get all those records, but the above loop is not working.

My final result should be LIKE :
f_table_type_id f_bet_amount f_win_amount
1 200 200
2 100 100
3 100 100

View 7 Replies View Related

SQL & PL/SQL :: Modify Number Data?

Mar 16, 2011

We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.

select * from temp;
id
----------
1056723

After update, it should be

id
----------
0456723

What function in pl/sql should be used for achieve the desired results.

View 6 Replies View Related

Table Has 2 Records With User ID - No Data Found

Mar 11, 2011

i got a prob in executing a query in my oracle.TRANSACTIONLIST is the table and my query is..

select * from TRANSACTIONLIST where USER_ID = '07751A1247'the table has 2 records with that user id.

But it is given result as "no data found".

View 4 Replies View Related

Importing Data From Different Schema Without Duplicating Records

Apr 27, 2011

I'm trying to figure out how to import data from another schema for an oracle SQL project in an MIS Course.Each student/schema created the following tables STUDENT, FACULTY, COURSE, IS_QUALIFIED, SECTION, IS_REGISTERED. Each student was supposed to make up names and ID numbers for 20 different students, then use real university faculty, courses, and sections to populate the respective tables.

I need to import data from all of another student's tables, without duplicating any courses, faculty, etc I already have in my tables.

The other student already granted acess to her tables, so now I'm just having trouble with the insert statement. This is what I have so far for the first table.

INSERT INTO myschema.STUDENT
SELECT * FROM otherschema.STUDENT

I know i need to add a "WHERE" statement to successfully copy the other persons data into mine, without duplicating any results. This is the last part of this project and I'm having trouble finding the answer anywhere.

View 1 Replies View Related

SQL & PL/SQL :: Group By Gives Wrong Value In Huge Data Records?

Jun 18, 2012

I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.

I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.

View 6 Replies View Related







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