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
ADVERTISEMENT
May 26, 2010
I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.
Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.
And it is still in VALID state
SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975
[code].....
View 14 Replies
View Related
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
Feb 28, 2012
I'm trying to add a column in an existing form. I added a text item and defined it as a database item. When I tried to rename the item the message (frm-15500 valid and unique object name must be entered)appears, and therefore not able to exit the property Platte.
Is there a way I can exit the Platte and should I've first add the column to the data block and then rename in my form ?
View 1 Replies
View Related
Jan 11, 2007
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
View 2 Replies
View Related
Oct 3, 2011
I am trying to ultimately as the title says separate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the
SELECT 'First Entry' Asset FROM Dual Union
SELECT 'Second Entry' Asset FROM Dual Union
SELECT 'Third Entry' Asset FROM Dual
and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.
View 5 Replies
View Related
Mar 28, 2013
I use APEX 4.2.0. In Tabular form, I have column of Select list type. This list has too many values and the end user must choose all these values along the tabular form rows. In other words, If the LOV consists of X, Y , Z, The End user should add three rows and choose a unique value for each row.
The problem is : This LOV is supposed to have too many values. What comes to mind is:
1- Whenever the end user picks a value, this value should disappear from the LOV of the second row ? But I have no clue how to do it?
OR
2- Whenever the end use picks a value, I should notify him/her about the remaining values that he should pick . But I have no clue how to do it ?
At the end the purpose is to make the user aware that he still has some rows to add ( values to choose ... )
I simplified the issue in simple example here:
[URL]......
workspace: somefeto
user: test
pwd: test
View 0 Replies
View Related
Apr 18, 2010
I have two different java process trying to insert in the same time in the same table for the same trade. The structure of the table Report is
create table report
( TRADE_ID NUMBER,
VERSION NUMBER,
MESSAGE_TIME TIMESTAMP)
There is a unique key on (TRADE_ID and VERSION) So if a new trade_id is inserted, the version is set to 1 and the second becomes 2 and so on. The version is calculated as last version of the trade_id ie. version + 1. It was woking fine till a new Java process was build that fired inserts through ten different java instances at the same time resulting in unique key error. So in detail what is hapenning is if three records of trade_id's comes in at the same time it should allocate versions in a first come first serve basis and there should be three versions of trade id 1,2 and 3. Now due to the multiple instances they all seems to get fired at once and all ending up with version one and thus resulting in unique key constrain error while trying to insert into the table.
View 3 Replies
View Related
Oct 31, 2008
I have 8 databases in multi master replication. I need to replicate only one rep object in to master sites. But I have 8 rep objects in the master definition site. is it possible to replicate the required rep object only without bringing the other rep objects in to replication.
View 10 Replies
View Related
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
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
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
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
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
View Related
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
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
Jul 3, 2013
I 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.
View 13 Replies
View Related
Jul 4, 2012
I have a table, a superclass and several derived classes.
the table definition looks like this:
/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
oid number,
transactid varchar2(200),
sessionid number,
hash number,
[Code]....
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsThe error occurs on 11.2.0.3.0 - 64bit as well.
As a workaround i'm not deleting the rows, but setting the stored objects to null. At the end of the routine i can truncate the table with the objects since truncating works. I'm not the administrator of the database, therefore i may not change any settings.
Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this workaround (simply don't truncate at the end), but leaves a lot of crap lying around.
View 4 Replies
View Related
Jul 6, 2010
I 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.
View 4 Replies
View Related
Jul 19, 2013
CREATE 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
View 4 Replies
View Related
May 6, 2013
I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
Desired Output:
======
First Second Third
11 21 31
I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31
Is there any way to get the output in single line.
View 3 Replies
View Related
Oct 24, 2013
create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
View 2 Replies
View Related
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
Sep 26, 2012
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
View 1 Replies
View Related
Jan 25, 2013
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
View 10 Replies
View Related
Dec 24, 2012
Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
View 5 Replies
View Related
Jun 15, 2010
currently developing a app for MSM in tromsø, that are going to delete the oldest entrys in the database table and then update all the relative Run history entrys in another table.
And i can't get the coding right to truncate the old entrys.Here are the
DECLARE V_SLETT varchar2(125);
V_NYE varchar2(50);
V_SLETT := :SLETT;
V_NYE := :NYESTE;
truncate DELTAKER ( IDNR in (V_SLETT) );
begin
request := UPDATE_RUN;
end;
View 15 Replies
View Related
Sep 8, 2008
In oracle 9i ........How to delete duplicate entries in a table ?
if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....
Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created
View 3 Replies
View Related
Apr 3, 2012
I am abolute new in PL SQL and I am working on following topic:
I have to check if an entry exists in a table.
- if it exists ...... do something
- if not ...... simply go on
I tried the following:
select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;
I fetched the result into a variable entries
if entries > 0 .....
MY problem is the following:
If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash
I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!
View 14 Replies
View Related
Jun 2, 2011
I have a table :
Product
A
B
C
D
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.
This would be done for all products for 4 years.
View 2 Replies
View Related
