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.
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare qry varchar2(5000); n number; alert number; Begin [code]....
I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.
my requirement in view like
And.......... And ((select count(*) from table A where conditions)=1 )then name is null AND
in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.
select am_obj_emp_obj(empno,ename,sal,deptno,service) from (select empno,ename,sal,deptno ,service from emp_vw where 1=1 and rownum < 2000 and service in ('MAN','SACH','SACL','KACL')) WHERE SAL = (SELECT MAX(SAL) FROM EMPS_VW WHERE DEPTNO = deptno or sal is null) union all
[code]...
This query is pulling 12 records of which 6 records are coming from the first query and the same 6 records is coming from the second query after union all Here am_obj_emp_obj is the object type and emp_vw is the view
Now I wanted to remove the duplicate records.When I implement the union operater it is giving me error.
ORA-22950: cannot ORDER objects without MAP or ORDER method.
I want to remove matching dbid's from the rman catalog without compromising te integrity of the catalog. I do however know a single way of removing them and its using the dbid and db_key which in my case are not not unique. The funny thing is they've running normally for a year now.
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
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.
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.
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?
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.
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
Created three tables and group by 3 tables column name. want to delete duplicate record without first table(test). Delete the duplicate record in test1 and test2 except test.
SELECT a as Name,b as M_Name, c as L_Name, count(*) FROM ( SELECT first_name as a, middle_name as b, last_name as c FROM test UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test1 UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test2 ) as countGROUP BY a,b,cHAVING count(*) > 1
here line_id is primary key .here i don't want to enter duplicate category code when i submit rows.Here Allocation_id and service_id would be same for particular service_id .How can i create validation to enter duplicate category code .
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.
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;
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare type type_name is table of varchar2(10); type type_name1 is table of type_name; names type_name1:=type_name1(type_name('hello')); begin -----HOW TO PRINT A VALUE-------- -----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT null; end;
1) need to print the values of names(1) 2)Assign a value to names(2)
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA APPEND INTO TABLE post1.thead WHEN (1:5) = 'THEAD' TRAILING NULLCOLS (file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine; Name Null? Type ----------------------------------------------------- -------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed Wrote file afiedt.buf
1 declare 2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME; 3 v_elapsed PLS_INTEGER; 4 type allobjects_record is record 5 (owner varchar2(1000) [code].......
ERROR at line 15:
ORA-06550: line 15, column 15: PL/SQL: ORA-00947: not enough values ORA-06550: line 15, column 3: PL/SQL: SQL Statement ignored
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
