SQL & PL/SQL :: Querying From A Primary Composite Key
May 20, 2010
I have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
View 7 Replies
ADVERTISEMENT
Jun 26, 2012
understanding composite primary key.
I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.
View 10 Replies
View Related
May 17, 2013
I am following Lynda Tutorial for Normalization. I have understand normalization.The core concept of primary key is that it uniquely identifies each record in the table, but here in the given below image the 'COURSE' field is repeating 'SQL101' value again and again but still the teacher in the video is calling it primary key and combination of date & Course column Composite key.how can a field which vales are repeating can be called as Primary Key?
View 12 Replies
View Related
Jul 4, 2013
I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.
Create Table Student
(
StudentID number (6,0) NOT NULL,
Student_Name varchar2 (20) NOT NULL,
Constraint Student_PK Primary Key (StudentID)
);
Create Table Faculty
(
FacultyID number (5,0) NOT NULL,
Faculty_Name varchar2 (20) NOT NULL,
Constraint Faculty_PK Primary Key (FacultyID)
);
Create Table Course
(
CourseID varchar2 (10) NOT NULL,
Course_Name varchar2 (20) NOT NULL,
Constraint Course_PK Primary Key (CourseID)
);
Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);
Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
);
This is the error I am receiving:
Error starting at line 1 in command:
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
)
Error at Command Line:8 Column:104
Error report:
SQL Error: ORA-02256: number of referencing columns must match referenced columns
02256. 00000 - "number of referencing columns must match referenced columns"
*Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
*Action: Make sure that the referencing columns match the referenced columns.
View 5 Replies
View Related
Jan 21, 2012
I have an employee table which has a primary key and a self referencing foreign key, as shown here
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Now if i make the primary key composite, as shown below -
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Oracle is throwing the following error -
ORA-02270: no matching unique or primary key for this column-list
How can this error be fixed without changing the composite primary key?
View 10 Replies
View Related
Jul 25, 2013
concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-
composite key
create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));
table created with composite bcz i took pair of id and pincode.
here base on the same example want to create candidate key with the syntax.
View 24 Replies
View Related
Oct 11, 2011
I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
View 4 Replies
View Related
Nov 8, 2012
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
View 1 Replies
View Related
Sep 24, 2013
problem with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
[code]....
View 13 Replies
View Related
Sep 5, 2011
I am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.
CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....
I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..
View 33 Replies
View Related
Jul 21, 2011
How can i apply composite key on a table?
View 2 Replies
View Related
Jul 5, 2012
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?
PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed
View 3 Replies
View Related
Jan 15, 2013
I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.
Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.
SELECT
SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS",
COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL"
FROM USER_REGISTRATION
WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))
ORDER BY MAILS DESC;
View 3 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
Feb 25, 2012
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
View 11 Replies
View Related
Feb 2, 2011
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
View 3 Replies
View Related
Aug 23, 2007
SELECT * FROM table WHERE id = $x
$x being a range of non-consecutive values like so:
1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
View 3 Replies
View Related
Apr 21, 2012
I have flattened customer dimension table and I would like to query it with other dimension table like address. I write a query, where I join address table twice to get permanent, secondary, and work addresses, but customer and address tables are huge that causing performance issue. Is any other ways to join flatten table with address dimensions than join it twice.
CREATE TABLE CUSTOMER
(
cust_sk NUMBER NOT NULL ,
cust_src_id VARCHAR2(20) NOT NULL ,
rec_eff_dt DATE NOT NULL ,
last_name VARCHAR2(75) NULL ,
first_name VARCHAR2(30) NULL
brth_dt DATE NULL ,
[code]......
View 6 Replies
View Related
Apr 16, 2013
I successfully created the following DBLink in my DEV environment:
CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';
I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.
select count(*) from hold@PROD.
The above query gives me 134 recs instead of 0!
Here is my TNSNAMES entry for PRD:
PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))
View 14 Replies
View Related
Feb 14, 2013
I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?
11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;
Table dropped.
Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....
- dynamic sampling used for this statement
22 rows selected.
View 8 Replies
View Related
Mar 17, 2011
I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.
SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');
The only test that I have been able to get working is the one below.
SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';
It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.
I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.
As an example this has not worked either...
WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;
So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.
Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.
===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)
[code]....
View 8 Replies
View Related
Apr 24, 2012
I am inserting data into a global temporary table and then using 'parallel' hint to query from this temporary table. I remember reading that the queries on the temp table may not run in parallel as the parallel sessions may not be able to see the data in the temporary table
However the execution plan as well as px_session, v$sql indicate that the query on the temporary table in fact run in parallel mode
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 7d68g52g0mskz, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(t,4) */ * from dbo_gtt t order by id,object_id
Plan hash value: 5815349
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------
| 1 | PX COORDINATOR | | 1 | | 99999 |00:00:01.46 | 3 | | | |
|
| 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 11M| 1311K| 424K (0)|
|
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 5 | PX SEND RANGE | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 6 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
|* 7 | TABLE ACCESS FULL| DBO_GTT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
select px_servers_executions from v$sql where sql_text like 'select%dbo_gtt t%';
PX_SERVERS_EXECUTIONS
---------------------
0
4
select sid, qcsid, server#, degree from v$px_session where qcsid = 228;
SID QCSID SERVER# DEGREE
---------- ---------- ---------- ----------
247 228 1 4
196 228 2 4
224 228 3 4
234 228 4 4
226 228 1 4
252 228 2 4
212 228 3 4
229 228 4 4
228 228
9 rows selected.
unfortunately I do not have access to get trace (tkprof) report. What must have happened during the execution?
View 3 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Apr 16, 2012
I have a table in Oracle with a column userid and i have a userid column in Active Directory. based on this i want to query the Network ID and update in one of the Group in the Active Directory. how to get the results?
View 2 Replies
View Related
Aug 14, 2012
We are using oracle database 11g R2 on REL 5 and i have an SQL server database used by one of our application. In my Oracle database, i want to query one table in my SQL server database and to link it in a table in Oracle database to compare data.
Is there a way like database link or something else to do this need. Send a link or pots here step by step the way on how to accomplish my query.
View 2 Replies
View Related
Oct 9, 2013
I have table with 129 million records.
If I just to select count(*) on the table its taking more than a minute in Sql Developer.
The table structure is as below, Primary key is a sequence and then 3 foriegn keys and one non-unique index on the date column.
<Table_Name>
column1 NOT NULL NUMBER ( Primary Key)
column2 NOT NULL NUMBER ( FK1)
[Code].....
View 1 Replies
View Related
Oct 14, 2013
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 28 Replies
View Related
Feb 10, 2012
We have a table with huge data which is skewed on a 'status' column. The 'status' column has 6 distinct values with 1 particular value occupying 80-85% records.
In the batch process we query the data on the status and process the retrieved records. My senior is insisting on partitioning which I see not much feasible considering cost implications just for a part of functionality
See there are 6 status 'A','B','C','D','E','F'
with 'A' occupying 80% records
'B' to 'F' occupies 2% till 14% records in the table(approx)
1) Create a conditional index on status (using case) to have records with all statuses except 'A' Then create If-ELSE structure
IF input parameter is 'A'
select /*+ FULL Parallel(t) */ * from t where status='A';
ELSE
Select /*+ INDEX (t conditional_index) */ * from t where status in ('B','C');
END IF;
I want to create conditional index here for 2 reasons
1] since it will have values for status except 'A' this nullify the chance that this index will be picked up when status='A' will be queried
Thus making the performance worst (status ='A' is for 80% records) - The IF-ELSE is additional protection
2] Less impact on the DMLS as the index will not be on status='A' which contribute to large chunk of records
2)Populate a dummy table which would contain rowid and status. Since the business closes at 21:00 and batch process starts at 21:30
Between these times periods refresh the dummy table every day using merge (to catch business transactions during the day)
Now during the batch process retrieve records from the main table using the rowids in the dummy table depending on the input status value
3)Create index on status
Make sure hard coded status values are used in the database procedures
Gather stats with the histograms
And leave it to the Optimizer to choose the best possible path
View 3 Replies
View Related
Nov 11, 2012
The Item data for individual cycles is as below.
Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A
The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.
In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.
Below is the code which I have written which considers the rundate as Input parameter.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
[code]....
In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.
Is there any way to query above only once and get the required results using Lag/Lead functions etc.
View 5 Replies
View Related
May 12, 2011
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
View 1 Replies
View Related
