PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

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


ADVERTISEMENT

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

I am using the below code to update specific sub-partition data using oracle merge statements.

I am getting the sub-partition name and passing this as a string to the sub-partition clause.

The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.

We are using Oracle 11gr2 database.

Below is the code which I am using to populate the data.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Include / Exclude In One Query Statement

Aug 31, 2011

how to write this query using all three tables in one query statement.

Result:

Input value of "GM" would return "GM@EMAIL.COM"

Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"

If "GM" is supplied, then only return the "GM@EMAIL.COM" email.

If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email

Test Case : GM would return "GM@EMAIL.COM" email

Test Case : GM and GMC would return "GMC@EMAIL.COM" email

select email
from email a,
company b,
sub_company c
where (company = "GM" but no match found in sub_company)
(company = "GM" and sub_company_name = "GMC" match is found in sub_company)

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

Case Statement In Where Clause

Feb 1, 2012

Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.

SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......

View 1 Replies View Related

Inner Join On Clause Statement?

Jul 21, 2011

I have 2 sql's statement below, and just wondering if their is difference between the two sql's.

FIELDS data type:
--------------------
a.field is DATE
b.field is also a DATE

SQL1:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field = b.field
WHERE a.field between b.field AND b.field + 2
;

SQL2:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field between b.field AND b.field + 2
;

OR

SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field >= b.field AND
a.field <= (b.field + 2)
;

which ever is correct between the two sql.

QUESTION: would be the two sql's generate same result set.

View 1 Replies View Related

SQL & PL/SQL :: Case Statement In Where Clause

Aug 10, 2011

select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and
( employee_status in(Case employee_status when {?Status}=1 then 'A'
when {?Status}= 2 then 'T'
When {?Status}= 3 then 'A'||','||'T'))
OR ( end_date >= {?START_DATE}
AND end_date <= {?END_DATE}
)
)

Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.

4 records for employee_status 'A'
3 RECORDS for employee_status 'T'
7 records for employee_status 'A' AND 'T'

How I should write a query to get 7 records.

View 2 Replies View Related

PL/SQL :: How To Use CASE Statement In Where Clause

May 16, 2013

How to use CASE stmt in WHERE clause?

View 3 Replies View Related

SQL & PL/SQL :: Syntax - Case Statement In Where Clause?

Mar 12, 2013

I am trying to use the following case statement in my where clause. My problem here is, I get no rows.

tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end

View 13 Replies View Related

PL/SQL :: Merge Statement - Delete Clause?

Mar 14, 2013

I was reading about merge statement and tried some variations,

create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);

insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;

[code]...

why is result different in this querys?

View 14 Replies View Related

SQL & PL/SQL :: Get Duplicate Values Using Delimiter

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

SQL & PL/SQL :: Duplicate Values In Two Columns?

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

SQL & PL/SQL :: Duplicate Values Removal

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

PL/SQL :: Duplicate Values - Getting Error?

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

SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query

May 27, 2011

I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.

SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );

[code]....

I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.

select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));

[code]....

Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.

View 3 Replies View Related

SQL & PL/SQL :: Filter Data Using CASE Statement In WHERE Clause?

May 29, 2013

Want to filter a data using CASE statement in WHERE clause for the following scenario.

Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.

where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END

View 3 Replies View Related

SQL & PL/SQL :: View Not Having ORDER BY Clause In SELECT Statement?

Jun 13, 2010

currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :

Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.

The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.

but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.

View 5 Replies View Related

Forms :: Display Duplicate Values Just Once

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

SQL & PL/SQL :: How To Eliminate Duplicate Values From Table

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

SQL & PL/SQL :: How To Restrict Duplicate Values From Query

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

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 View Related

SQL & PL/SQL :: Detect And Move Duplicate Values

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

PL/SQL :: Remove Duplicate Values In Collections?

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

PL/SQL :: Delete Duplicate Values From A Table?

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

SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause

Mar 28, 2011

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

View 6 Replies View Related

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

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

PL/SQL :: Check For Any Duplicate Values In COUNT() Column

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

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

With Clause - Use Values Of Cteas In Update?

May 2, 2013

with cteas
as
(
select hit from radial,gib
where no=id
)
select hit from cteas

[code]...

for the above query can i use the values of cteas in update .because it throws error cteas as table/view not found

if i use like this am getting error in update (missing select key word)

with cteas
as
(
select hit from radial,gib
where no=id
)
update xenon
set nub=(select hit
from cteas
where ren=hit)

View 3 Replies View Related

Using Array Values In IN Clause Of Select Query?

Sep 9, 2008

I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

View 13 Replies View Related







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