Retrieve Distinct Values From Clob Using Distinct Operator
May 27, 2013i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: How To Retrieve CLOB Data Using DISTINCT Keyword For Other Columns
Aug 2, 2012I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
SQL & PL/SQL :: How To Retrieve Distinct Cat_id
Nov 1, 2011Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)
Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER
select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc
How can I retrieve distinct cat_id here?
SQL & PL/SQL :: How To Extract Full Text From Clob Where Having Distinct Select
May 23, 2013I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?
select distinct
o.id "Organization ID",
en.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",
[code]....
SQL & PL/SQL :: Distinct Values In A Column?
Jul 18, 2013I have a table with two columns, like:
123 xxx
456 xxx
789 yyy
987 yyy
And in the output I would like to have:
123 xxx
789 yyy
I tried with distinct and unique on the second column, but it doesn't work.
SQL & PL/SQL :: Distinct Values In A Hierarchical Result?
Nov 7, 2011I have a table with 4 columns. The data is stored in an hierarchical format where L1 being the parent and L4 being the lowest child.
L1 L2 L3 L4
1 11 111 1111
2 21 211 2111
2 22 222 2222
[code]...
So each Level(L1 ..L4) has zero or many child levels which further has more levels.With out using PL/SQL how can we write a Select query to give me a distinct of all children, all the way to the lowest level (L4).Example: give me all the children where L1 = 3.Result: 31, 32, 33, 311, 322, 333, 3111, 3222, 3333Is it possible to write such a query or am I asking too much logic out of a select and should go with PL/SQL.
SQL & PL/SQL :: Getting Distinct Values String Using LISTAGG?
Jun 3, 2013CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;
[code].....
Query Output :
1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh
Expected Output :
1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh
Expectation here is duplicate values should not be repeated.
SQL & PL/SQL :: Create A String With Distinct Values
Aug 11, 2010Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a cursor, whose sql is returning seven rows with these values:
9
4
4
9
7
9
4
i open cursor and fetch these values into variable as shown
OPEN id_search FOR l_sql_stmt;
LOOP
FETCH id_search INTO l_eve_id;
if l_eve_id != l_eve_id_prev then
l_eve_id_str := l_eve_id_str || ' , ' ||l_eve_id ;
[code].......
but i want only the distinct values in l_eve_id_str , i.e.,
l_eve_id_str := 9,4,7
What this code is doing is creating a string with all the values
l_eve_id_str := 9,4,4,9,7,9,4
How to remove duplicates from this string?
SQL & PL/SQL :: Distinct Values - Get Records For Which All Startdate Same
Jul 13, 2010I have table as below :-
Table ABC(
ID Number,
startDate date,
City varchar2(10)
)
I need to write query which will get me all the CityNames for which there are no Startdate differs,
i.e. To get all the city name records for which all of the Startdate are same across all the records.
I dont want to go after Self Join due to performance issues, do we have any better way?
PL/SQL :: Fetch DISTINCT Values From Partitioned Table
May 12, 2013What is the fastest way to fetch DISTINCT values from partitioned table?
1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)
Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
CNTRY_KEY NUMBER,
STUDY_ID NUMBER,
PRD_KEY_YEAR NUMBER,
PRD_KEY_WEEK NUMBER,
DATE_FROM DATE,
[Code]...
-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)
** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR
[Code]...
** Explain Plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 6,235 Bytes: 629 Cardinality: 37
8 HASH UNIQUE Cost: 6,235 Bytes: 629 Cardinality: 37
7 CONCATENATION
3 PARTITION RANGE ITERATOR Cost: 1,985 Bytes: 1,031,900 Cardinality: 60,700 Partition #: 3 Partitions accessed #194 - #207
[Code]...
Partition #: 7 Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.
SQL & PL/SQL :: How DISTINCT And UNION Eliminating NULL Values
May 3, 2012As per NULL values concept
One NULL value is not equal to other NULL value.
But how DISTINCT and UNION eliminating NULL values.
Then how UNIQUE key constraint accepting more than one NULL value..
PL/SQL :: Select Records Based On First N Distinct Values Of Column
Sep 25, 2012I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
Distinct On Presorted SQL
Oct 7, 2010I have a function which returns a preformatted SQL but with duplicates as follows
FSI
..FSIL
..FSIL
....IS123
....IS123
....IS345
....IS345
....IS547
....IS547
..FSIR
..FSIR
....IS98777
....IS98777
....IS34567
....IS34567
....IS67799
....IS67799
I have to eliminate the above result set without changing the order. a distinct on the function returns a jumbled result set.
SQL & PL/SQL :: Distinct Value From VARRAY?
Feb 21, 2012I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.
declare
type t is varray(10) of varchar2(10);
t1 t;
type r is table of varchar2(10) index by binary_integer;
r1 r;
begin
t1 := t('A','B','A','B','A','B','C');
select distinct * into r1 from table(select * from t1);
END;
SQL & PL/SQL :: Select Just 1 Row For Each Distinct Column Value?
Feb 16, 2011i need a Select * from tablename just 1 Row for each distinct ActionMode column value
CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),
[code]...
SQL & PL/SQL :: Getting DISTINCT Count From Two Different Columns?
Feb 1, 2012I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
This query gives following result
3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR
There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result
3525125350000 2
SQL & PL/SQL :: Use Distinct For Multiple Fields?
Oct 18, 2011i have a query in this way
select field1,field2,field3 from Table1
union
select field1,field2,field3 from table2
In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...
SQL & PL/SQL :: Multi Column Distinct
Sep 16, 2010I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
SQL & PL/SQL :: Distinct With Order By Clause?
Mar 13, 2012I have got the following error while executing below Query.
ORA-01791 'Not a SELECTed expression'
select distinct sgbstdn_levl_code
from sgbstdn,spriden
where spriden_pidm = sgbstdn_pidm
and spriden_id = '200076543'
order by sgbstdn_term_code_eff desc;
The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.
SQL & PL/SQL :: Distinct With Orderby In Subquery
Mar 31, 2010The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:
SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid
But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.
SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)
Is there any way to select the DISTINCT taskids ordered as per requirements?
SQL & PL/SQL :: Distinct Timestamp - One Entry Per Day?
Apr 21, 2010how can I distinct this query to get just one entry for one day.
this query:
select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp
returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.
but i want to have:
Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.
SQL & PL/SQL :: Merge Two Table With Distinct Value
Sep 4, 2012select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'
when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?
PL/SQL :: Distinct Preceding Value Required
Sep 12, 2012Following on from this answered thread: (xmlagg(xmlelement) - Distinct Values Required..Whereby the last poster recommended the use of
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)
To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".
Sample data:
create table xml_test
(emp_number number, seq number)
insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
[code]....
SELECT DISTINCT On Multiple Columns
Sep 17, 2010I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
How To Count Distinct Characters From A String
Sep 26, 2005how to count different characters from a string ....
View 7 Replies View RelatedSQL & PL/SQL :: How To Populate Distinct Column In A Table
Apr 22, 2013I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
View 3 Replies View RelatedSQL & PL/SQL :: Distinct On Multiple Table Inner Join
Apr 28, 2010I am little confused applying DISTINCT on a Multiple table Inner join.
Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com
Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4
Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4
Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4
QUERY::
select distinct c.process_id a.role_id,a.email_address,c.process_name
from role a, notification_role b, process c
where a.role_id=b.role_id and b.process_id = c.process_id
SQL & PL/SQL :: Get The Distinct Elements Of A Collection Of Objects
Oct 7, 2010I have a collection of objects built in a PL/SQL program.
I want to keep only the distinct elements of this collection for the rest of the program.
I failed to find the correct way to do it
For instance (the actual object is far more complex containing object attributes itself), with the following types:
drop type t1b
/
drop type t1a
/
create or replace type t1a as object (
v1 integer,
v2 integer
)
/
create or replace type t1b as table of t1a
/
I have the following variable:
v t1b := t1b(t1a(1,2),t1a(1,2));
And I want to get only one "t1a(1,2)" in my collection.
My first idea (actually the second one but it does not matter) was to use DISTINCT:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select distinct t1a(v1,v2) bulk collect into v from table(v);
5 dbms_output.put_line(v.count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4
As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).
The next idea was to use multiset operators:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select v multiset intersect distinct v into v from dual;
5 dbms_output.put_line(v.count);
6 end;
7 /
1
PL/SQL procedure successfully completed.
This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.
SQL & PL/SQL :: Export Based On Count Distinct
Jun 7, 2013Is there a way to export records based on a select distinct into different xls or csv files in oracle? I could be using SQL Developer.
The following will generate 74 unique values which each contain approx 3000 records.
select distinct(telespornr from vingelen2012
SQL & PL/SQL :: Count Distinct Rows In Table
Feb 21, 2013I think this is easier than I think but all I want to do is count the distinct rows in a table.
WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL
[Code]..
seems to get what I want
ID1VAL1CNT1
111114
212224
321114
422224
but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1
NOT what I want
ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253