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

SQL & PL/SQL :: Generate Unique Combination

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

SQL & PL/SQL :: Part Number And Manufacturer Combination Must Be Unique?

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

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

Non Unique Index Using A Non Unique Field

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

SQL & PL/SQL :: Combination Of Values

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

Forms :: Key Combination To Get Another Form

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

SQL & PL/SQL :: Previous Record For Multiple Combination

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

SQL & PL/SQL :: Oracle - Generating Combination Of Numbers?

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

SQL & PL/SQL :: Locate Occurrence Of Symbols Combination

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

PL/SQL :: Output Only Combination Of 2 (col1 Value) And 1 (col2 Value)?

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

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

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

PL/SQL :: Find Out Column / Combination Of Columns From Given Table

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

SQL & PL/SQL :: Cross Join Query To Remove Repeating Combination

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

Forms :: Oracle 10g Key Combination To Call Form Trigger

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

SQL & PL/SQL :: List All Tables In Database With Particular Record Values Combination

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

Forms :: List Item Values Showing With ALT + DOWN Key Combination

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

SQL & PL/SQL :: To_char Function In Combination With Distinct Clause - ORA-00936 / Missing Expression

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

PL/SQL :: Finding The Max Value

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

SQL & PL/SQL :: Not Finding The Foreign Key?

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

PL/SQL :: Finding ID Max (salary)

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

Finding 10 Most Recent Missions

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

Finding Alert Log File

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

Finding Primary Key In A Table?

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

Finding Unused Index

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

Finding Permission Tables

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

SQL & PL/SQL :: Finding The Difference Between IN And EXIST?

Nov 22, 2011

finding the difference between IN and EXIST.

View 15 Replies View Related

SQL & PL/SQL :: Finding Session IDs Initiated By Particular Job

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

SQL & PL/SQL :: Finding Values In A Range?

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

SQL & PL/SQL :: Finding Duplicate Records

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







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