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 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.
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.
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
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.
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 RelatedI 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 RelatedI have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.
View 44 Replies View RelatedI need a query to get the below.
Source :
select * from test;
LVL
1
2
3
1
2
3
4
Output:
LVLSEQ
11
21
31
12
22
32
42
I need above to uniquely identify the set of data.
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.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
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 have a problem with some tables in database.
Table has three columns: userid, pname, pvalue.
Userid has unique values, for example: 234, 123, 587, etc.
In the field pname, there is three possible values: MAC, IP, S/N.
So if I go like this:
select pvalue
from table
where pname = 'MAC';
i get the values of MAC.
If I go like this:
select pvalue
from table
where pname = 'IP';
i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.
I have a list sample:
Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA
[code]....
What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:
Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE
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.
i am having a table with out pk along with data.
Now , i need to add one column to that table , and update this column with the sequence no
like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.
how can i do.
I am stuck with this query.
I have a table "xyz" as -->>
SQL> select * from xyz;
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.
Either of SQL or PLSQL would d for me.
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).
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.
Row_Number() invalid identifier
View 6 Replies View RelatedI am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:
Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.
I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.
How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?
can I rewrite the following query without using the 'ROW_NUMBER() OVER ' part.The query is supposed to pull out the records whose CODE is not NULL and has most recent date for UPDATE_DATE . The reason I wanted to do this is, When I embed this query in between many other queries along with JOINs, My oracle server is unable to execute. So, I thought its better to supplant 'ROW_NUMBER() OVER ' logic with something else and try it. .
CURRENT QUERY:
SELECT a.* FROM
(SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.PIDM
ORDER BY b.UPDATE_DATE DESC) AS Rno
FROM
(
SELECT *
FROM SHYNCRO WHERE CODE IS NOT NULL
)b
)a
WHERE a.Rno = 1
We have generated a fancy custom interactive report, in which the columns of string type, but can hold string, numeric or date values. However the type of values are uniform for a particular column. The end users wants to use the numerical values for generating charts, but, being a string column (though the data is numeric), it throws an error and they could not generate the chart. Is there any way to make use of this string column type numerical data for generating chart?
The current version of apex is 4.1.
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?.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View Relatedi 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.
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
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 RelatedFrom 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?