PL/SQL :: Unique Values Of Each Column
Aug 21, 2013I 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
ADVERTISEMENT
SQL & PL/SQL :: Multiple Unique Values In One Column?
Aug 26, 2011I 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.
SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences
Apr 17, 2013i 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.
Reports & Discoverer :: Get Unique Values With Formula Column
Jul 5, 2013I 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]....
SQL & PL/SQL :: Get Unique Row Number For List Of Values?
Apr 9, 2012I 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.
SQL & PL/SQL :: How To Retrieve Unique Values From A String
Feb 5, 2013Objective: 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
SQL & PL/SQL :: Finding Tricky Unique Values
May 30, 2013I 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).
SQL & PL/SQL :: Get_hash_value Generated No Unique Values
May 8, 2012We 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?
SQL & PL/SQL :: How To Not Generate Row_number For Unique Values
Jun 6, 2013I 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.
Grabbing 2 Unique Values Based On A Field?
Nov 29, 2012I 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.
SQL & PL/SQL :: Unique Values - Print Output Of Table
Dec 9, 2011I 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.
Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?
Feb 19, 2011In 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).
Forms :: Performing Validation Based On Unique Values In A Form
Mar 10, 2013In 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.
SQL & PL/SQL :: How To Get Unique Value In Column
Oct 25, 2012i 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.
SQL & PL/SQL :: How To Add Column In Unique Index
Jan 17, 2011I 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 >
SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null
Jun 15, 2012I have 3 columns in a table: colX, colY, colZ.
Trying to find a way to prevent duplicates with these, but only if colX is not null.
For example, if there are already values for: colX = 1, colY = 1, colZ = 1
then:
Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1
I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.
I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.
Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.
ORA-02270 / No Matching Unique Or Primary Key For Column-list
Jan 14, 2013I'm trying to make a foreign key against a table which has a unique index instead of a primary key.
i get this error:
Error SQL: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:
CREATE TABLE TABLE1(
IDTABLE1 NUMBER(5),
NAME VARCHAR2(30)
);
CREATE TABLE TABLE2(
[code]....
or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)
SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries
Jul 22, 2011I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.
I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.
CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);
best way as it does a self join on big table of 9.5 million.
(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))
What are the better ways to rewrite this query?
SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed
Jul 3, 2013I have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table
Dec 23, 2010My 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]...
Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list
Jul 6, 2010I was cloning a schema user1 as user2 in the same database.
user1 had quota on 2 tablespaces user1_data and user1_index.
I created user with name as user2.
I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows
exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y
then imported in in user2 schema as follows
imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y
during import i encountered following errors for so many constraints
"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
I found that the it happened as the primary key of table1 was not created for which error was logged in the log file
. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported
However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?
I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.
I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.
There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.
PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column
Jul 25, 2013From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedApplication Express :: Hyperlink Column With Link Parameters As Region Column Values
Apr 10, 2013On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
Application Express :: Can Pass More Than 3 Column Values With A Column Link
Jun 19, 2012I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies View RelatedSQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column
Jul 19, 2013CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
Non Unique Index Using A Non Unique Field
Aug 14, 2013Using 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?.
SQL & PL/SQL :: Column Values To Column Names
May 20, 2011I had a table( T1). Five columns exists in the table. The table data is like below
C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five
I need the output like below one
JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive
SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values
May 4, 2010find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
SQL & PL/SQL :: Max Row Values Into Column
Oct 3, 2011I have table output as shown below.
AID UCD U_TXT UDATE PID
116 1 Req Documents 01-OCT-2011 100
116 2 AGG APPR 01-OCT-2011 101
116 3 Docs received 02-oct-2011 102
116 1 Tmp received 02-oct-2011 103
117 2 Notice sent 03-oct-2011 104
UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116.
PID - Primary id (Primary key column)
Output I am looking
--------------------
AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC
116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011
117 2 Notice sent 03-oct-2011
If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID
I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post
SQL & PL/SQL :: Column Values In Row
May 10, 2011i've follow problem:
select x1
from table
and i get
a
b
c
What i want is
a,b,c
The main problem here is, that it can be
a
b
as such as
a
b
c
d
e
f
in detail, i don't know prior how many i will get. With declare a cursor is it easy, but i try to find one solution only with sql.