How To Make INSTR Search DISTINCT

Feb 9, 2010

I have a script that is using the INSTR function to search through a block of data for a specific string (CALL). I am ONLY looking for that string set but unfortunately, there are other words within that block of text that have that string set within it (e.g. CALL_MY_PHONE). Is there any way to make the INSTR search DISTINCT? Below is the code that I am using:

to_number(DECODE(INSTR(ph.block,'CALL'),0,0,1))

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Instr Versus DBMS_LOB.instr / Search A Pattern Backwards In A CLOB Field?

Mar 10, 2012

I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.

Parameters: instr(text_to_be_searched, pattern, offset, nth)

Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.

select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;

View 6 Replies View Related

SQL & PL/SQL :: Search String From The End To Beginning (reverse INSTR)?

Sep 22, 2010

Trying to get a number out of an error backtrack

06512: at "SCHEMA.PROCEDURE", line 4

I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.

The database languages might differ so I can't do this using

v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)

As it would not find any 'line' inside the string.

View 7 Replies View Related

Retrieve Distinct Values From Clob Using Distinct Operator

May 27, 2013

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

Application Express :: Enable IR Search Field To Search In Hidden Columns

Jul 15, 2012

I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?

if not, that means I have to :

1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.

but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.

how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.

View 7 Replies View Related

SQL & PL/SQL :: INSTR With Replace Function

Aug 27, 2013

I am trying this function

instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0

i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2

View 1 Replies View Related

How To Use Substr / Instr To Isolate

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

View 3 Replies View Related

SQL & PL/SQL :: Instr Function And Index Usage

Apr 14, 2012

I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.

CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);

The below query is going for full table scan due to this.

select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2

how to recreate the above index so that these queries uses this index.

View 16 Replies View Related

SQL & PL/SQL :: Substr And Instr In Update Statement

Nov 28, 2010

I have a table1 with columns serial no and name as below

serial no name
1 john paul
2 john victor

and i have another table1 with columns serialno,firstname and second name as below

serial no firstname secondname
1
2

I want to update my table2 with the name from table1 and divide and insert in first name and second name as below

serialno firstname secondname
1 john paul
2 john victor

i use the query as below

update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)

but the above query not worked.

View 11 Replies View Related

SQL & PL/SQL :: SUBSTR / INSTR - Isolate MVNAME Schema?

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

But its proving a bit tricky to nail it.

View 6 Replies View Related

SQL & PL/SQL :: Using INSTR In Where Clause To Give COMPLETE MATCH Strings?

Dec 9, 2011

We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .

The procedure builds the string by inserting single quotes in the following manner.

P_BU_LST is the parameter which have comma separated values

'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value

i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';

This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS

ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;

INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577

here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.

But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.

View 8 Replies View Related

Distinct On Presorted SQL

Oct 7, 2010

I 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.

View 1 Replies View Related

SQL & PL/SQL :: Distinct Value From VARRAY?

Feb 21, 2012

I 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;

View 1 Replies View Related

SQL & PL/SQL :: Select Just 1 Row For Each Distinct Column Value?

Feb 16, 2011

i 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]...

View 5 Replies View Related

SQL & PL/SQL :: Getting DISTINCT Count From Two Different Columns?

Feb 1, 2012

I 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

View 9 Replies View Related

SQL & PL/SQL :: Use Distinct For Multiple Fields?

Oct 18, 2011

i 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...

View 3 Replies View Related

SQL & PL/SQL :: Distinct Values In A Column?

Jul 18, 2013

I 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.

View 12 Replies View Related

SQL & PL/SQL :: Multi Column Distinct

Sep 16, 2010

I 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.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Distinct Cat_id

Nov 1, 2011

Category 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?

View 21 Replies View Related

SQL & PL/SQL :: Distinct With Order By Clause?

Mar 13, 2012

I 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.

View 14 Replies View Related

SQL & PL/SQL :: Distinct With Orderby In Subquery

Mar 31, 2010

The 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?

View 13 Replies View Related

SQL & PL/SQL :: Distinct Timestamp - One Entry Per Day?

Apr 21, 2010

how 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
.
.

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Table With Distinct Value

Sep 4, 2012

select * 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?

View 1 Replies View Related

PL/SQL :: Distinct Preceding Value Required

Sep 12, 2012

Following 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]....

View 7 Replies View Related

SELECT DISTINCT On Multiple Columns

Sep 17, 2010

I'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.

View 3 Replies View Related

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: How To Populate Distinct Column In A Table

Apr 22, 2013

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

SQL & PL/SQL :: Distinct On Multiple Table Inner Join

Apr 28, 2010

I 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

View 3 Replies View Related

SQL & PL/SQL :: Get The Distinct Elements Of A Collection Of Objects

Oct 7, 2010

I 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.

View 2 Replies View Related

SQL & PL/SQL :: Export Based On Count Distinct

Jun 7, 2013

Is 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

View 25 Replies View Related







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