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
ADVERTISEMENT
Mar 1, 2010
I want to delete the duplicate rows in a table without using ROWID.
I have the following data.
SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.
SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
View 8 Replies
View Related
Apr 7, 2012
I want to delete the duplicate records from a table without using Below 2 methods:
1> delete from table_1 a where row_id not in (select min(row_id) from Table_1 b where a.PK=b.PK);
2> Insert into Table_2 select distinct * from Table_1;
Drop table Table_1;
Rename Table_2 to Table_1;
View 20 Replies
View Related
Aug 7, 2012
I looking for a cursor where i can find duplicate rows in a table. Like i have emp table in which i have deptno column. I have four with same deptno e.g 10. Now my requirement is after i get the 1 record with deptno 10 , i need a message that shows remaining 3 record as duplicate entry except the very first entry.
View 1 Replies
View Related
Jun 30, 2011
how to display the dupicate record in table
View 1 Replies
View Related
Jul 14, 2010
Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.
View 10 Replies
View Related
May 12, 2011
I have written this below code. The logic behind the code is, Delete the duplicate record from a table, and delete those record from other 7 table based on the SL_NUMBER.
But Problem is After delete the duplicate record When I have use Below statement
RETURNING SL_NUMBER BULK COLLECT INTO rec_sl_number;
This statement unable to return approx 40 Lakhs SL_NUMBER
DECLARE
rec_sl_number dbms_sql.number_table;
BEGIN
[Code]....
View 6 Replies
View Related
Nov 4, 2013
I am using forms 6i, I want to insert a new record in the form based on an existing record (so I do not have to type all the values)
is there a shortcut key that I can use to copy the entire record and then when I say insert new, I just paste the record I just copied
View 4 Replies
View Related
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
Jun 19, 2012
I have one form in which there are master detail blocks. I am entering one record in master block and corresponding entry in detail block. If again I am entering a new record in master block, of course the corresponding entry is getting erased since the block is getting changed.
After entering the data in master block I want to pop a message as 'whether you want to duplicate the same entry in detail block '. If yes, then how can I copy the same details which i have entered for previous record? Can I use duplicate_record built in? If yes, How?
View 1 Replies
View Related
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
Nov 24, 2011
I need to delete the duplicate values from plsql table OR move the distinct values in plsql table to other plsql table.
how can i do this ?
DECLARE
TYPE alist IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
p_tbl alist;
BEGIN
p_tbl(1) := 'A1';
p_tbl(2) := 'B2J';
p_tbl(3) := 'A1';
[code]......
The p_tb1 table contains all the above values including duplicates. Now I need only distinct values to be copied in another plsql table of same type.
View 14 Replies
View Related
Aug 22, 2012
I have a table like this
table:
id name plan code
1 sam normal 5
1 sam normal 6
1 sam special 5
1 sam Special 6
I need to delete data in such a way that one entry with normal and one entry with special plan should remain and should be with different code. Does not matter whether normal stays with 5 or 6 code.
I tried with rowid but it deletes either both normal or both special or returns same code for normal and special.
View 8 Replies
View Related
May 13, 2012
i have master-detail form.in master my bill_id gets generated when new form is open and i copy the same bill_id in detail(tabular)for each item.all the items which i enter in detail form get save the same bill_id which got generated.
in another form(which is tabular) i want to display bill_id's from detail form.but in detail form . There are same bill_id's more than once.but i want to display those bill_id's which are more than once only once.
View 1 Replies
View Related
Oct 10, 2011
we have a table attendance_d with no constraint which have duplicate emp_id we want to stop duplicate emp_id on the same date. if employee's record already entered in today's date then duplicate Error message must show if he tries again to enter the same record. for this i have written the following code but it is not working date wise some body. i want to use on WHEN VALIDATE ITEM TRIGGER in oracle forms 6i.
DECLARE
l_count NUMBER;
BEGIN
[Code]....
i have tried my best to format the syntax of code but in preview it showing like as above i have formated in toad by using the key ctrl+shift+f.
View 2 Replies
View Related
Feb 2, 2012
I'm looking for a way to select 25% of a view.. I need to do this four time to get all the data. The size of the view changes constantly (I need to split my select because of heavy load on an application)
something like this:
Select * from MyView where [some condition that makes only 0-25% show ]
Select * from MyView where [some condition that makes only 25-50% show ]
Select * from MyView where [some condition that makes only 50-75% show ]
Select * from MyView where [some condition that makes only 75-100% show ]
I tried to search in the forum but found nothing.. maybe ROWID could be used to accomplish this somehow?
View 5 Replies
View Related
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
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
Jun 6, 2012
I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:
create table ticket
(
id number(10) not null,,
ticket_type number(1) not null,,
is_sold number(1) not null,
CONSTRAINT ticket_pk PRIMARY KEY (id)
);
[Code]...
id ticket_type is_sold
------------ -------------------- -----
10000004 1 1
10000005 2 1
10000006 1 0
10000007 1 0
10000008 2 0
10000009 2 0
SQL> select * from customer_ticket;
cust_id cust_name ticket_id
------------ -------------------- ----------
1 John 10000004
2 Sara 10000005
my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.
Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!
I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't work.
View 13 Replies
View Related
Sep 21, 2011
I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
View 8 Replies
View Related
May 28, 2013
I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)
Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.
I have difficulties to merge these data. Below is what I tried
CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;
CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
[code].........
View 10 Replies
View Related
Jun 16, 2011
How do you repeat the same values to the next record from the previous record to reduce the user entry in d2k ??
View 1 Replies
View Related
Sep 1, 2010
I am using forms 10g...
I have 2 blocks,in one i insert student_id and when i click on the button i should get all his courses in the other block...
BUT.. when the student has more than 1 course I get this:
when button pressed trigger raised unhandled exception ORA-01422
Here is the button
begin
select TEST_STUDENT_INFO.stud_name,TEST_STUD_CRSE_DETAIL.crse_no,
TEST_COURSES.crse_name,TEST_STUD_CRSE_DETAIL.crse_type,TEST_STUD_CRSE_DETAIL.crse_type_desc,
TEST_COURSES.crse_time
INTO :stud_name,:crse_no1,:crse_name,:crse_type1,:crse_type_desc1,:crse_time
from TEST_STUDENT_INFO,TEST_COURSES,TEST_STUD_CRSE_DETAIL
where (TEST_STUD_CRSE_DETAIL.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_STUDENT_INFO.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_COURSES.CRSE_NO = TEST_STUD_CRSE_DETAIL.CRSE_NO)
and (TEST_STUD_CRSE_DETAIL.CRSE_TYPE = TEST_COURSES.CRSE_TYPE);
end;
but if the student has ONLY ONE course it WORKS FINE!
View 4 Replies
View Related
Feb 16, 2010
Oracle 10g
In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.
select update_date from table1
where update_date between sysdate and sysdate-90
The above query retrun no data even data is there in the table for this range.
Update_date
11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
[code]...
View 7 Replies
View Related
Apr 13, 2012
I have an LOV on my form which holds a list of course units for a student to select and insert. However some units on the LOV must be COMPULSORY(not optional).... so i was wondering is there a way to have these auto selected from the LOV?
View 4 Replies
View Related
Mar 28, 2011
I am developing form, but there is issue when I Press F11 to query data.I make trigger form when-new-record-instance to assign values for item.But when i Press F11 then Block no clear.
View 1 Replies
View Related
Jul 9, 2013
I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?
View 1 Replies
View Related
Apr 8, 2012
I have a requirement to delete duplicate records. For example,if the below query retrieves count of duplicate records along with the column values.
select col2,col3,col4,col5,col6,col7,count(*) from table
group by
col2,col3,col4,col5,col6,col7
having count(*) > 1;
I would like to retain only one record with max(col1) which is a surrogate key and other records should be deleted.How to retain one record in a duplicate record set based on max of certain column.
View 14 Replies
View Related
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
Jan 26, 2011
I have following query:
type EMP_REC is record
(
id number,
name varchar2(20),
dept number
);
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER;
EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1
FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
View 1 Replies
View Related
