SQL & PL/SQL :: Finding UNIQUE Combination
Oct 4, 2012
I have contents like below:
BREAD,BISCUIT
BREAD,MILK,BISCUIT
COKE,MILK
MILK,SUGAR
Now combination from each row will be like below (Just for understanding):
BREAD,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {BISCUIT,BREAD}
BREAD,MILK,BISCUIT --> [COMBINATION] {BISCUIT} {BREAD} {MILK} {BISCUIT,BREAD} {BISCUIT,MILK} {BREAD,MILK} {BISCUIT,BREAD,MILK}
COKE,MILK --> [COMBINATION] {COKE} {MILK} {COKE,MILK}
MILK,SUGAR --> [COMBINATION] {MILK} {SUGAR} {MILK,SUGAR}
Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:
BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5
COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25
MILK = 3/4 = .75
[Code]....
writing query to find unique combination like above? What I have tried is as below:
create table test (row_no number, col_no number, item varchar2(50))
/
insert into test values (1,1,'BREAD');
insert into test values (1,2,'BISCUIT');
insert into test values (2,1,'BREAD');
[Code]....
But I am not able to form the exact query. let me know if this can't be done through a single query.
View 20 Replies
ADVERTISEMENT
Dec 17, 2011
I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10
B:20
C:20
D:20
E:30
F:40
G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
View 31 Replies
View Related
Aug 31, 2010
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
[Code]....
View 2 Replies
View Related
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
Aug 14, 2013
Using Oracle 11g, below is the table, partitions, unique and non-unique local index:
CREATE TABLE DOCA( DOCA_ID NUMBER NOT NULL , DOCA_BKG_PAX_ID NUMBER NULL , ROW_PURGE_DATE DATE NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))( PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')), PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),) TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX XPKDOCA ON DOCA( DOCA_ID ASC, ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0; ALTER TABLE DOCA ADD CONSTRAINT XPKDOCA PRIMARY KEY (DOCA_ID); CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA( DOCA_BKG_PAX_ID ASC)LOCALREVERSETABLESPACE I0;
I would like to know the difference between the performance of the unique and non-unique local indexes?.
View 10 Replies
View Related
Jan 7, 2011
I have a requirement in this format.
These are the scripts for the 2 input tables
CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
[Code].....
The output needs to be in this format
key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40
Is there a good way to dynamically obtain this output either by PL/SQL or by query?
This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this
View 6 Replies
View Related
Mar 19, 2010
I have a requirement here. User wants when he pressed 'CTRL+E' on the form item, he must get another form. Actually 'CTRL+E' is defined for EDITOR. Then how could I achieve this?
View 2 Replies
View Related
Aug 17, 2012
How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query
Test Scripts as below
CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......
Expected output as in attached sheet.
View 11 Replies
View Related
Oct 25, 2010
I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
writing the stored procedure generating the output for this
View 18 Replies
View Related
Aug 5, 2011
My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).
Ex:
select 'jeklghje, s.Glkgje, u.slgjwek, 904869' as tt from dual union all
select 's.Tklgj, u.slgjwek, 23578, elslgjs' as tt from dual union all
select 's.klgj, u.ekgjes, 238573, dlsjkgj' as tt from dual
I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".
I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be useful, but I'm not familiar with these functions so good yet.
View 7 Replies
View Related
Mar 19, 2013
I have data like:
Col1 Col2 Col3
1 1 N
1 1 N
1 1 Y
2 1 N
2 1 N
2 1 N
I need in output only combination of 2 (col1 value) and 1 (col2 value). i.e. consider only those records where all records of key combination (col1, col2) have 'N'
View 3 Replies
View Related
Sep 29, 2011
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
View 5 Replies
View Related
Sep 23, 2013
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies
View Related
Nov 15, 2010
I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
View 3 Replies
View Related
Nov 11, 2009
I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.
80 : 1 : "Shift+P" : 64 : "Duplicate Record"
It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.
View 2 Replies
View Related
Jul 1, 2013
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob
7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
View 3 Replies
View Related
Jan 9, 2013
In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.
View 1 Replies
View Related
Mar 2, 2012
I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.
When I use distinct clause inside a to_char function it gives the following error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
The Sql is
select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales
I can't see any syntax error in the sql..but forsome reason, it doesn't work.
View 2 Replies
View Related
Jun 19, 2013
I have the following query where i need to find the max value[code] with t as
( select 'L1' R_nm ,'Data' R_Data , 'Obj' R_Obj , 'Wd' r_prec , '2' val
from dual union all select 'L1' , 'Data', 'Obj' , 'No', '4'
from dualunion all select 'L2' , 'Data', 'Obj' , 'No', '4' from dual )
select t.*, max(val) over(partition by r_nm,r_data,r_obj) rk from t[/code]
My expected output should be
[code]
r_nm r_data r_obj r_prec val rkL1 Data Obj Wd 2 1L1 Data Obj No 4 2L2 Data Obj Yes 1 1
[/code]
View 1 Replies
View Related
Dec 4, 2011
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK
[Code] ............
There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:
ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found
I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..
View 2 Replies
View Related
Oct 31, 2012
Select * from one:
ID SALARY
----------------------
10 1000
20 2000
30 3200
Select * from two:
ID SALARY
----------------------
10 1000
20 2000
40 4000
10 3000
20 4000
30 3000
When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:
Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;
OUTPUT:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
30 3200
10 3000
I want OUTPUT to be like this:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
View 3 Replies
View Related
Jun 23, 2008
What i have is table with:
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m
SET m.security_level = (
SELECT max(m2.security_level)
FROM missions m2
WHERE m2.mission_type_id = m.mission_type_id
AND length(m2.code_name) > 7
)
WHERE m.mission_ID IN (
Select m3.mission_id
From missions m3
ORDER BY m3.mission_id desc)
View 3 Replies
View Related
Dec 23, 2011
How can i come to know that which current alert log file is being used for database? Is their a command at database level to find out the current alert log file to which database is using ?
View 2 Replies
View Related
Oct 4, 2007
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies
View Related
Nov 3, 2008
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
View 8 Replies
View Related
May 30, 2011
How to find the list of tables which has permission select only and tables which has both read and write access permission for a particular user.
I'm using ORACLE database.
View 1 Replies
View Related
Nov 22, 2011
finding the difference between IN and EXIST.
View 15 Replies
View Related
Feb 7, 2012
I want to track the session id that has been initiated by a particular job (I have job ids with me). Which dictionary views I can get this result.
View 1 Replies
View Related
Nov 1, 2011
@test.sql
accept 1 num prompt 'enter begin value '
accept 2 num prompt 'enter end value '
Need to display all values between the above given inputs.
No tables involved.
Need SQL level solution in version 8i.
View 4 Replies
View Related
May 20, 2010
i have table with name, count, flag with dublicate records
example
with swayam name , counts are 3, 4
with ramana name, counts are 5,5
with reddy name, counts are 1,2,3
i want to update the flag
if count are same then update one of record (flag='A') and other should be flag='R'
if count are different then update the max count (flag='A') and other should be reject remaing (flag='R'). use below quires
CREATE TABLE TEST_DUB ( NAME VARCHAR2(99), V_COUNT NUMBER, FLAG VARCHAR2(1));
Insert into TEST_DUB (NAME, V_COUNT)
Values
('SWAYAM', 3);
Insert into TEST_DUB
(NAME, V_COUNT)
Values
[Code]....
View 4 Replies
View Related