Return Minimum Duplicate Values
Dec 16, 2009
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
View 4 Replies
ADVERTISEMENT
Jul 22, 2010
SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.
View 2 Replies
View Related
May 30, 2013
The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!
SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....
View 7 Replies
View Related
Apr 28, 2011
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies
View Related
Aug 2, 2010
whether a function can return two values?
View 4 Replies
View Related
Aug 26, 2010
I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.
Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.
View 1 Replies
View Related
Dec 19, 2012
the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.
CREATE OR REPLACE PROCEDURE test IS
cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;
View 14 Replies
View Related
Aug 28, 2010
I want multiple values from a function. I want to use this function in a SQL query. Here i'm giving my try.
SQL> CREATE TABLE TEMP
2 (
3 ID NUMBER(1),
4 SAMPTYPE VARCHAR2(20 BYTE),
5 SALARY NUMBER(10)
6 )
7 /
Table created.
SQL> INSERT INTO TEMP VALUES(1,'ABC',10000);
1 row created.
SQL> INSERT INTO TEMP VALUES(2,'PQR',20000);
1 row created.
SQL> INSERT INTO TEMP VALUES(3,'JPD',5000);
1 row created.
SQL> COMMIT;
Commit complete.
[code]...
Here i get result as ABC*10000, but i want two separate values as ABC,10000. how can i do this via function.
View 6 Replies
View Related
Nov 29, 2012
I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.
CREATE OR REPLACE PROCEDURE check_values (
my_values IN emp.dept_no%TYPE,
p_cursor OUT sys_refcursor
)
[Code]....
/The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.
How can I resolve this issue?
View 15 Replies
View Related
Mar 13, 2012
I have a table second_table which has a username and code field. A username (not the primary key) may be entered multiple times with different codes, or a single code 'ALL', in which case, the codes have to be fetched from 'third_table'. I am unable to form a 'case' clause to handle the fact that a list has to returned when the 'code' field from the second_table returns 'ALL'.
e.g.
Second_table
username code
A ALL
B 23
B 56
B 33
Third_Table
code
67
78
So, when the user asks the codes for user A, he should get 67 and 78 and when he asks for the user B, he should get 23,56 and 33
View 13 Replies
View Related
Apr 8, 2013
I am using functions to return multiple values of two rows or multiple rows.
For example emp id = 100 and i need to return the value for this(empid) input and output of this first_name and salary.
I am tried in this way below but got errors (ORA-00932: inconsistent datatypes: expected NUMBER got HR.EMP_TYPE)
create or replace type emp_type as object(first_name varchar2(20),salary number);
create or replace function f1(empid in number)
return emp_type
as
emp_record emp_type;
begin
select first_name,salary into emp_record.first_name,emp_record.salary from employees where employee_id = empid ;
return emp_record;
end;
/
select f1(100) from dual;
View 12 Replies
View Related
Sep 1, 2010
I have table with below values
SELECT * FROM dup_val
1
1
1
1
2
2
2
2
3
The result set should be like
1-1
1-2
1-3
1-4
2-1
2-1
2-2
2-3
3-1
the query get the above result set.
View 10 Replies
View Related
Apr 25, 2011
How to find the duplicate values in two columns.
Suppose we have two columns A and B and the data looks like this
A B
--- ---
1 One
2 Two
1 One
2 Two
3 Three
I need to write a query in such a way that i should find out the duplicate values which are repeating.
View 7 Replies
View Related
Aug 6, 2010
Yesterday (05/08/2010) i have mistakenly inserted duplicate values in the tables, every value is inserted 2 times in a table.
so upto my knowledge the data were correct upto 30/Jul/2010. hence i need to recover the data from this 30th Jul date.
View 4 Replies
View Related
Jun 19, 2012
I have two tables
Table1
Id1 Name1
1 Jack
2 Jack
3 John
Table2
ID2 Name2
NULL Jack
NULL John
I would be assigning ID2 from ID1 based on name match.As Jack has 2 ids when I use the statement
UPDATE TABLE2
set id2 = (select distinct ID1 from table1 where table1.name1=table2.name2);
I get an error message as select statement would return more than one row and the update statement fails completely. with the sql statement to update the ID2 as error when we have duplicate records and continue with the update for other records.
like
table
ID2 Name2
ERROR Jack
View 2 Replies
View Related
Jul 3, 2013
I have a PL/SQL function where i call java class. In java i`m making some calculations and i need to return to body of PL/SQL function some values ( like 6 to 10, few numbers, string, 1 xml and 1 html).
I will show pl/sql function..
CREATE OR REPLACE FUNCTION GET_TAB(P_CURS SYS_REFCURSOR) RETURN TYP1_TAB PIPELINED IS
OUT_REC TYP1:=TYP1(NULL,NULL,NULL,NULL);
V_NAZWA VARCHAR2(5);
V_NUMER NUMBER;
[code].....
View 26 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
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
Sep 15, 2012
I have below query.. When i run this query i need to get two rows.. But i am getting two more duplicate rows.. I want to restrict these two rows..
How can i do this.. Here the problem is when i join B query then only i am getting duplicate rows..
SELECT DISTINCT B.TIC_ID, B.TIC_ISS_NO,
B.TIC_NUMBEC||CEV_ID,
B.TIC_NUMBEC, B.CEV_ID, B.AOSTED_DATE,
B.COMAANY_CODE, B.CONTCACTOC_NAME,
B.FC_CODE, B.C_NO, B.FC_TYAE,
[code].......
View 4 Replies
View Related
Mar 16, 2010
Table contains duplicate data . Have to move data to another table. Criteria: check for duplicate values if duplicate exist move all duplicates except one to the history table. While moving to other table see if the record being moved already exists.
source table
SOURCE TABLE : ODS_OWNER
grp_id grp_name face_id address1 city zipcode
3456789 NIKE AERO 457899 707 CROFT GRAND RA 12345
1256789 NIKE AERO CORP 678899 707 CROFT SE GRAND RA 12345
5465455 BB SHIPPING 809708 201 SOUTH CT DESPLAINE 45434
[Code]....
FIRST 4 RECORDS ARE DUPLICATES FROM WHICH 1 RECORD GOES TO w_grp AND ONE GOES TO HISTORY TABLE. THE RECORD WHICH GOES INTO w_grp OUT OF THE DUPLICATES WILL DEPEND ON THE LAST MODIFIED DATE FOR EACH
DISTINCT VALUES GO IN w_grp TABLE
DUPLICATE GO INTO match_his TABLE
View 3 Replies
View Related
Mar 22, 2013
is it possible to remove duplicate values in plsql collections without using multistage operators ?
plsql collections output:
ID NAME
----- -------
001 A
001 A
002 B
003 C
004 D
005 E
005 E
005 E
expected output
ID NAME
----- -------
001 A
002 B
003 C
004 D
005 E
View 3 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
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Feb 26, 2013
I have the following database function.
GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);
The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78
what data type I should use so that my output is returns all the decimal values?
View 5 Replies
View Related
Sep 20, 2012
Imagine I have the following function:
FUNCTION normalize(str IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN TRANSLATE(LOWER(str),
'äàáâãăāåąæčçðďéèëêěĕėęğģġîĭïīìíłļľŀñńňņöóòôõσøőřśŝšşţüúùûǔųūůŵýÿżźžżαβßγδεζŋηικλμµνξπρσςτυφω',
'aaaaaaaaaaccddeeeeeeeegggiiiiiillllnnnnoooooooorsssstuuuuuuuuwyyzzzzassydeznniklmmnxprsstufo'
);
END;
I'm tired to add missing characters in this list...
What I would like is that all characters with an accent or diacritics should be replace by their "base" letter (ë -> e) and Greek letters to be replace by their corresponding values (ω (omega) -> o)
View 1 Replies
View Related
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
May 13, 2013
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE
2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y
3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
View 2 Replies
View Related
Jul 15, 2013
Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement? Priview SQL statement
SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)
[Code]....
In this case i got duplicate of entry TT2 id 50513 In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)
View 5 Replies
View Related
Feb 15, 2012
Well i like to display the Values in Boiler Plate/Text. For Example, i have Report with Address Columns. for Tin No: i have Hard coded as TIN: 34XXXXXX01 and i like to change this hard coded item as TIN: 34XXXXXX02 so during Runtime if the input is changed the above item to be changed.
Inputs given in Report Parameter;
if the input is given as 81
TIN: 34XXXXXX01
if the input is given other than 81
it should print as TIN: 34XXXXXX02
How to give it in the Boiler plate.?
View 6 Replies
View Related
Nov 22, 2012
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
View 6 Replies
View Related