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.
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.
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.
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).
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?
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.
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.
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.
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.
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
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]....
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).
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.
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.
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?
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.
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.
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
