SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

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.

View 1 Replies


ADVERTISEMENT

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

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

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

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

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.

View 11 Replies View Related

Reports & Discoverer :: Get Unique Values With Formula Column

Jul 5, 2013

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

View 2 Replies View Related

PL/SQL :: Updating A Column Randomly With Selected Values?

Sep 26, 2013

 We've an accounts table that basically represents hotel chains & brands. an example shown below. 

account_id chain_id brand_id  service1              
NULL     NULL          111              
NULL     NULL          122              
NULL     NULL           11

 Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.

There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,

chain_id  brand_id         
101     2011          101    
2012          102     2020 

Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg., 

account_id chain_id brand_id service1              
101          2011     111               101         
2011     122               102          2020     11 

so each account can be attached to only one chain_id and one brand_id.

View 2 Replies View Related

SQL & PL/SQL :: How To Update Column Value Without Using Sequences

Apr 17, 2013

I am having a table with out a primary key .It also contains data.

My requirement is to add a new column to the table and update the column value with unique and sequential number , starting from 1 to end of rows.

This has to be done without using a sequence.

View 2 Replies View Related

SQL & PL/SQL :: Get Unique Row Number For List Of Values?

Apr 9, 2012

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

View 25 Replies View Related

SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

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

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

SQL & PL/SQL :: Get_hash_value Generated No Unique Values

May 8, 2012

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?

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

Grabbing 2 Unique Values Based On A Field?

Nov 29, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Unique Values - Print Output Of Table

Dec 9, 2011

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.

View 11 Replies View Related

SQL & PL/SQL :: Updating Old Values In Trigger?

Apr 16, 2013

I am learning oracle trigger, i have one query.

Can i increment the old column value in trigger.

eg: :new.cid := :old.cid+1;

is this is correct.

View 4 Replies View Related

Find Situations Where Single Unique Value Of Field A Has Both Values Z AND X In B?

Feb 19, 2011

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

View 1 Replies View Related

Forms :: Performing Validation Based On Unique Values In A Form

Mar 10, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

SQL & PL/SQL :: Pattern Matching And Updating Of Number Values In String?

Nov 5, 2011

I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:

#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0

Here, I want to update the above field value to a value in the form:

#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0

This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).

View 4 Replies View Related

Forms :: Updating Records Values And Post Trigger

Jul 15, 2013

I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.

The block has 5 items as follows:

1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'

The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.

I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.

Consider this scenario...

The block has 3 records.

record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.

I check the RECORD_STATUS checkbox on record2.

(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).

I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.

I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.

(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)

How can i stop this from happening?

View 1 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

PL/SQL :: Updating CSV Values In Oracle Table Based On Condition

Aug 27, 2013

I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.

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

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 :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

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

View 4 Replies View Related

SQL & PL/SQL :: Updating Column In Every Row Except For The First

Jan 16, 2012

I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:

SQL> select * from example1;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225

12 rows selected

I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:

SQL> select * from example2;

CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225

12 rows selected

I have done some searches and haven't seen any results.

View 12 Replies View Related

SQL & PL/SQL :: Updating Nth Row And Adding Column

Aug 7, 2012

I had created a table which have 100s' of entries in it.

create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***

[Code]..

1. In this table, how to update the Nth row, how can I do it

2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.

View 30 Replies View Related

PL/SQL :: Updating Column Value If Any DML Operations Done

Apr 12, 2013

I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.

ex:

id name
-- -------
1 xxxx
2 yyyy
3 zzzzz
4 pppp
5 rrrrrr

if i delete

delete from test where id=4;

then automatically.. "id" column values will again in sequence... like this

id name
-- -------1 xxxxx
2 yyyyy
3 zzzzzz
4 rrrrrr

note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....

ex : insert to test values(seq_name.nextval,'tttt');

id name
-- -------
1 xxxxx
.
.
.
4 rrrrr
5 ttttt

View 5 Replies View Related

ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jan 14, 2013

I'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)

View 11 Replies View Related

SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries

Jul 22, 2011

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

View 2 Replies View Related







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