SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

Objective: I need to compile a final string by concatinating the unique values from different strings.

Here is the script to create tables and data.

Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');

COMMIT;

select * from temp_acronyms;

ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO

I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Retrieve First Letter Of Every Word From String

Oct 4, 2013

I want to retrieve first letter of every word from the string.

For ex. if string is "Computer Science Engineering (New)" then I want "CSE" or if string is "Mechanical Engineering" then "ME".

how to do this..

View 18 Replies View Related

SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies View Related

SQL & PL/SQL :: Copy Related Data From / To Same Tables - Combined With Changed String And Unique ID

Aug 12, 2010

I'm trying to copy a particular set of rows into two tables. I want to change one varchar column to a different string in one of the tables, and create a new unique ID for the copied rows that will match on both tables. I also want that new unique ID to be one digit higer than the current highest unique ID being used across the tables as a whole.

create table testing_query_dtl
(query_is number not null, query_type varchar2(20) not null, area_code varchar(4), stn_code varchar2(3))
create table testing_query_hdr
(user_name varchar2(30) not null, query_indicator varchar2(1) not null
, query_created date not null,
query_last_used date not null,
query_name varchar2(40) not null
, query_is number not null)
[code]...

I then used 'mymax+test_seq.nextval' but that won't avoid the unique constraint issue on the table testing_query_hdr and on the table testing_ query_ dtl increments the ID on EVERY row even when I don't want it to.

View 4 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

SQL & PL/SQL :: Get Unique Row Number For List Of Values?

Apr 9, 2012

I need a query to get the below.

Source :

select * from test;
LVL
1
2
3
1
2
3
4

Output:

LVLSEQ
11
21
31
12
22
32
42

I need above to uniquely identify the set of data.

View 25 Replies View Related

SQL & PL/SQL :: Finding Tricky Unique Values

May 30, 2013

I have a table which stores customer ID and price lists assigned to those customers. Each price list can have multiple customers assigned to them. So for example

Customer APrice_List1
Customer BPrice_List1
Customer C Price_List2
Customer D Price_List3

Now I am trying to write a SQL statement to return just pricelists which contain just ONE customer (so in the above example would just return Price_list2 and Price_List3).

View 3 Replies View Related

SQL & PL/SQL :: Get_hash_value Generated No Unique Values

May 8, 2012

We are using the function dbms_utility.get_hash_value to create a unique identifier, the hash is created based on statement of creating an index. We see that for a different statement is being generated the same value, which in this cause errors because this value must be unique.

The Hash is to be calculated as follows HASH_VALUE = dbms_utility.get_hash_value (sql_text, a 10000)

We are using the correct the hash function or should we use another way to generate unique values?

View 9 Replies View Related

SQL & PL/SQL :: How To Not Generate Row_number For Unique Values

Jun 6, 2013

I don't want to generate row_number for unique values 'C' and 'E' in below query.

SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) FROM
(SELECT 'A' NAME FROM DUAL
UNION ALL
SELECT 'A' FROM DUAL
UNION ALL
SELECT 'A' FROM DUAL

[code].....

Means row_number should be NULL for unique values.

View 12 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

Reports & Discoverer :: REP-0002 Unable To Retrieve String From Report Builder

Nov 25, 2010

My own created software is running since last 4 years but now when I am trying to run a report then I got a message "REP-0002 unable to retrieve a string from the report builder" and as a result my report don't run. Please note that some other reports are working properly, this problem is happens only with some of them reports.

View 3 Replies View Related

Grabbing 2 Unique Values Based On A Field?

Nov 29, 2012

I have a list sample:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA

[code]....

What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE

Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.

View 1 Replies View Related

SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

i am having a table with out pk along with data.

Now , i need to add one column to that table , and update this column with the sequence no

like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.

how can i do.

View 1 Replies View Related

SQL & PL/SQL :: Unique Values - Print Output Of Table

Dec 9, 2011

I am stuck with this query.

I have a table "xyz" as -->>

SQL> select * from xyz;

A B
---------- ----------------------------------------
1 Hello
2 Hello
3 No Hello
4 No Hello
5 Hello
6 Hello
7 Hello

I want to print the output of this table as -->>

A B
---------- ----------------------------------------
1
2 Hello
3
4 No Hello
5
6
7 Hello

To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.

Either of SQL or PLSQL would d for me.

View 11 Replies View Related

PL/SQL :: How To Compare Two Rows And Retrieve The Values

Aug 15, 2012

I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.

For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.

I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.

Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.

I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0

View 16 Replies View Related

Reports & Discoverer :: Get Unique Values With Formula Column

Jul 5, 2013

I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003

For leavetype

FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....

View 2 Replies View Related

Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?

Feb 19, 2011

In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).

To go into some detail -

I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),

however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,

so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).

View 1 Replies View Related

Forms :: Search Criteria - Retrieve Values In 9i?

Jul 19, 2010

I would like to know how can I retrieve both values below in rms forms 9i.

Both values pertains to one id.
id - 00012345 (with zeroes)
id - 12345 (w/out zeroes)

The id is use as one of earch criteria.

View 3 Replies View Related

PL/SQL :: Retrieve Item With None Of Its Char Values As Null?

Oct 12, 2012

I have a table something as below

Things_t
Things Characteristic Char Value
Item 1 Colour Red
Item 1 Packaging
Item 2 Shape Square
Item 2 Brand Spunk

Now i want to reterive an item with none of its char values as Null. Using the query “ select distinct things from things_t where char value is Null ” will fetch the item 1 also together with item 2. i want to fetch a record from thing for which none of the char values are Null such as Item 2.

View 9 Replies View Related

Forms :: Performing Validation Based On Unique Values In A Form

Mar 10, 2013

In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.

My requirement is as below,

While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve Values If User Entered In Mixed Case

Apr 23, 2008

how to retrieve values if user entered in Mixed case means (case insesitive)?

We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.

How can write the query to get this kind of mixed case data?

View -1 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Multiple Values / Possibility From A Table?

Jun 1, 2010

I have a table called table1 and its structure is as follows:

rtrn_id number,
entp_abn number,
litm_line_item_nbr number
country_code varchar 2(10)

I am trying to find retrieve value/row where for a particular rtrn_id , the possibility of having a country code of "CA"(for Canada) and "US" or "HK" (US or Hong Kong respectively) exists.

I framed my query the following way and it does not retrieve any result in Production and I believe there are a few values/rows in the "table1".

The query framed is:

Query 1:

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010 and prog_program_cd = '01'
and sched_nbr = '000' and (litm_line_item_nbr = '023' and country_code 'CA')
and (litm_line_item_nbr = '016' and country_code = 'CA')

The above query does not retrieve any rows

Where as if I run a query individually it retrieves tons of rows

(Individual query :

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code = 'CA')

-----Retrieves tons of rows as the country code = Canada for line item # 16

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code 'CA')

-----Retrieves tons of rows as the country code IS NOT Canada for line item # 23.

There is a possibility that there are rows that have country Code as Canada and not Canada for a particular ID whose line item numbers could be 16, 23.. is there any better way to frame my Query1 to retrieve values/rows?

View -1 Replies View Related

Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table

Apr 19, 2010

My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.

I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.

View 2 Replies View Related

PL/SQL :: Split String Values

Feb 28, 2013

I have a column in a table that contains a string seperated by .

e.g.

IT.HARDWARE
IT.APPS
IT.SOFTWARE

I would like to split the two values out on two columns e.g.

Column1 - IT
Column2 - Hardware
etc.

View 3 Replies View Related

PL/SQL :: Filter Values From String

Oct 13, 2013

I have data something like this:

Sample DateWITH DATA AS          (          SELECT 'AAAXXXX IO BLUEEXPRESS' LIST FROM dual          UNION ALL          SELECT 'BLUEEXPRESS AAAXXXX IO BLUEEXPRESS'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD DDDDD AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX NO CARBON'  FROM dual          UNION ALL          SELECT 'NO CARBON
[code]....

The above result depends on the following rules:         

- Replace BLUEEXPRESS into BEXPRESS         
- Remove the term NO CARBON (See row no 6)         
- Reduce all multiple space into single space (see last record).

So far I create separated queries for replacing BLUEEXPRESS into BEXPRESS and replace NOCARBON term but I don't know how to do it in a single shot as well as stuck on scenario to remove multiple spaces  and put single space. 

View 5 Replies View Related

SQL & PL/SQL :: How To Find Greatest Values In A String

Jul 22, 2011

I am trying to update the greatest value in a column from a string of other column.

Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only

so for that I had written query as follows:

update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))

The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.

View 7 Replies View Related

SQL & PL/SQL :: Getting Distinct Values String Using LISTAGG?

Jun 3, 2013

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

View 1 Replies View Related

SQL & PL/SQL :: Create A String With Distinct Values

Aug 11, 2010

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

View 8 Replies View Related

PL/SQL :: Extract Values From String To Variables?

Jul 16, 2013

I need to extract values from string to variables as below.

declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or  varchar2(100);
l_s  varchar2(100);
l_ot  varchar2(100);
l_pre  varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/

But here challenge is order of alignment change dynamically. ex as below. 

str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';

 So how to extract Acknowledgement to l_a,Order requester to l_or ...

View 9 Replies View Related

PL/SQL :: String Having Null Values Between Commas

Dec 29, 2012

My DB version is 10.1.0.5.0

I want extract the values from the string using below query but i am unable to bring the correct result.

WITH t  AS ( select '123,1,3,22' col FROM DUAL
        UNION ALL
        SELECT '123,,2,1' FROM DUAL
        UNION ALL
        SELECT '5,1,2,,' FROM DUAL
        )

[Code]...

My required result like below.

123     1      3      22
123            2       1
5       1      2       

get required result using regular expressions.

View 3 Replies View Related







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