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


ADVERTISEMENT

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

How To Generate Unique Identifier

Jun 21, 2012

Is there a way to generate a unique identifier(length 8), which can contain numbers (0-9) and letters(a-z) in pl/sql or sql ?

Note :- in oracle 9i.

View 23 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

PL/SQL :: Generate Unique Number Without Sequence?

Feb 13, 2013

I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

I am having ORA-00937 : not a single-group group function error

View 23 Replies View Related

SQL & PL/SQL :: Generate Unique Numbers Without Sequences

Dec 20, 2010

i had a requirement to generate unique numbers without sequences.

For Example: i had a table called "Test".Test has two columns.

ID and Name are 2 column names.Primary key constraint exists on column(ID).

How to generate the ID values without using sequence and Stored procedures.

View 9 Replies View Related

Generate Random And Unique 6 Digit Number In Oracle

Sep 29, 2008

I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.

View 3 Replies View Related

Forms :: Generate Unique Document Numbers When More Than One User Inserting In Form At One Time

Dec 5, 2011

I have made one application form where users need to enter some data. This data is getting inserted in four tables. As in for now the data is properly getting saved and retrieved only for one user at a time. But problem arrives when more than one users are simultaneously making an entry and saving the data at one time.Same number is getting generated for the users who are saving the data at one time which should not happen.

View 4 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

SQL & PL/SQL :: How To Get Unique Value In Column

Oct 25, 2012

i am using one query which should return unique value. I have one table that has one column punch date datatype is date.

here value is stored in two rows that is

1. 24-10-12
2. 24-10-12

and my query is this:

SELECT distinct(PunchDate) FROM Trans_RawProcessDailyData ORDER BY PunchDate ASC;

but still getting two values.

View 11 Replies View Related

Non Unique Alternate Key Of A Table

Feb 11, 2011

Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.

View 8 Replies View Related

Unique Constraint Violated?

Mar 26, 2007

From a step by step instructions I'm asked to put the following into sql*plus:

CREATE TABLE Lab2Lecturer
(staffNO VarCHAR2(10) NOT NULL,
title VARCHAR2(3),
fName VARCHAR2(30),

[code]...

Then the following:

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode, country, lecturerLevel, bankNO, bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Johanna','Santoso',
'3 Robinson Av', 'Kew', 'Melbourne', '3080', 'Australia', 'C', '1000567237', 'CommBank', 65000.00,1.0, 'O-R DB');

and finally,

INSERT INTO Lab2Lecturer
(staffNO, title, fName, lName, streetAddress, suburb, city, postCode,country, lecturerLevel, BankNO,bankName, salary, workLoad, researchArea)
VALUES
('1000', 'Dr', 'Justine', 'Martin', '6 Algorithm AV', 'Montmorency', 'Melbourne', '3089', 'Australia', 'D', '1000123456', 'CommBank', 89000.00, 1.0, 'CBR');

when I try entering in the second one I get an error 'unique constraint violated'.So whats wrong exactly?

View 1 Replies View Related

SQL & PL/SQL :: Unique Index Shorting

Jun 18, 2012

I have a running application where i have a table with pk defined on it and the pk clumn is inde in asc order by default. Can i alter the index in desc order as i always need to see the data in desc order.

View 7 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

SQL & PL/SQL :: How To Find Unique ID When Several Matches

Feb 1, 2012

I have a simple question, but i can't resolve it.

I have a table like this :

ID_A | VALUE
-------------
A 1
A 2
B 1
B 2
B 3
C 1
C 3
D 1

I need to fid the unique ID that match perfectly VALUE = 1 or 2.

I tried SOME/ANY/ALL/IN

View 14 Replies View Related

SQL & PL/SQL :: How To Get First Transaction For Every Unique Client

Nov 2, 2010

I have a table called pf_stock_txns which just stores all of a clients transactions

If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.

However, I need a query that will give me the first transaction for every unqiue client.

View 17 Replies View Related







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