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.
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.
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 ;
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.
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.
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.
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.
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?.
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;
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?
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
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.
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'
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.
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.
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.
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 ?
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.
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.
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.
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