SQL & PL/SQL :: Cross Referencing Query From A Single Table
Mar 21, 2012
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
View 1 Replies
ADVERTISEMENT
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
Jun 26, 2013
I'm planning to upgrade a small database (~150GB) from 10.2.0.3 on windows 2003 23bit to 11.2.0.3 RAC on Linux 5.8.The database contains oracle spatial too. A suitable method and link to document to be followed.
View 2 Replies
View Related
Nov 15, 2010
I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
View 3 Replies
View Related
May 25, 2012
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
View 1 Replies
View Related
Jan 27, 2012
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
View 10 Replies
View Related
Jan 18, 2012
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well
2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query
pls guide me with some query example
View 2 Replies
View Related
Mar 13, 2013
I am working in all 3 environments: development , Test and Production. How can we access the tables created in one environment in the other environment. Also, request to share the SQL code for the same.
I am using Oracle Sql Developer tool.
View 4 Replies
View Related
May 10, 2012
can we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
View 1 Replies
View Related
Jan 11, 2011
I'm having a bit of a problem getting the syntax of a trigger right. Unfortunately, I have no DBAs locally, I use some third party software, and for reasons beyond my understanding, I have been told to use triggers, and not stored procedures, so I'm running with it.
The set up:
STRUCTURES table: contains several columns, one of which is the unique ID column.
ASSAY table: contains several rows, also with the same ID column, but can have more than one row per ID (several assays per compound). One column is XC_ASSAYS.
The idea of the trigger is basically: When a row in the ASSAYS table is updated, pull out the ID of the row, then calculate the average of the XC_ASSAYS columns for those rows, and report it to the STRUCTURES.XC_ASSAY column for that row ID.
My best attempt thus far results in compilation errors.
CREATE TRIGGER INHIB_W_ALA_TR
AFTER INSERT OR UPDATE ON ASSAYS
FOR EACH ROW
BEGIN
UPDATE STRUCTURES SET XC_ASSAY = (SELECT AVG(XC_ASSAY) FROM ASSAYS WHERE ASSAYS.ID = :NEW.ID) WHERE STRUCTURES.ID = :NEW.ID
END;
/
The resulting errors are:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/190 PL/SQL: ORA-00933: SQL command not properly ended
12/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
[code]...
I don't understand some of the errors, such as why line 2 SQL is ignored (it seems correct?), or I'm supposed to properly terminate the trigger (I've read ; and /, but I'm getting the end-of-file errors when I do so). Tried shuffling syntax and ' or " around - and I can't get it.The body SQL works when I replace :NEW.ID with an actual variable (such as 'NMP12'), but I'm not sure how to pass the ID variable from the updated row into the body. The ID is not updated, but other columns are.
View 3 Replies
View Related
Jun 27, 2013
I have a table called dept table with nested collection as below. Nested Table Collection:
create or replace
type courselist as table of varchar2(64) Table Using Nested Table Collection:
CREATE TABLE "FCSDWH_STG"."DEPT"
( "NAME" VARCHAR2(20 BYTE),
"DIRECTOR" VARCHAR2(20 BYTE),
"OFFICE" VARCHAR2(20 BYTE),
"COURSES" "FCSDWH_STG"."COURSELIST"
) Content Of Table:NameDirectorofficecoursesAccountingJames CharlesUNOFCSDWH_STG.COURSELIST('natural science','chemistry','Computer Science','Computer Science')
I am trying to select and print an element from nested table collection using below plsql block.
MY_COURSE VARCHAR2(64 CHAR);
BEGIN
SELECT courses INTO MY_COURSE FROM TABLE(SELECT COURSES FROM DEPT) where courses='chemistry';
DBMS_OUTPUT.PUT_LINE(MY_COURSE);
END; Error Message:
PL/SQL: ORA-00904: "COURSES": invalid identifier
View 3 Replies
View Related
Aug 5, 2013
How can I reference an Object Type created on a remote database?This is the escenario:
In DATABASE A: CREATE OR REPLACETYPE USERA.EXPO_EXPEDIENTES_RECAUDOS OID 'DCADCB2EA2344DFAB1D205C03D708359' AS OBJECT ( exer_cd_expediente VARCHAR2 (50), exer_id_ident_expediente VARCHAR2 (30), exer_cd_sucursal NUMBER (3), exer_cd_ramo NUMBER (2), exer_nu_poliza NUMBER (7), exer_nu_certificado NUMBER (9), exer_nu_contrato NUMBER (7), exer_cd_nacionalidad VARCHAR2 (1), exer_nu_cedula_rif NUMBER (9), exer_nm_titular VARCHAR2 (70), exer_st_expediente VARCHAR2 (2), exer_de_status_exp VARCHAR2 (240), exer_fe_status_exp DATE, exer_cd_productor NUMBER (5), exer_nm_productor VARCHAR2 (60), exer_cd_mail_productor VARCHAR2 (50), exer_in_habilitado VARCHAR2 (1), exer_in_permite_habilitar VARCHAR2 (1), exer_in_carga_consulta VARCHAR2 (1), exer_cd_ramo_aplicacion VARCHAR2 (200), exer_cd_producto VARCHAR2 (6) )/ In DATABASE B:
After creating the public synonym and asigning the required privileges on the object in DATABASE A, I try to execute the following:
DECLARE x EXPO_ EXPEDIENTES_RECAUDOS;BEGIN null;END;
But i got the following error:
ORA-06550: line 2, column 12:PLS-00331: illegal reference to
USERA. EXPC_ EXPEDIENTES_RECAUDOS@DATABASEA
After investigating a little i found the following, but i dont know how to apply it, "The CREATE TYPE statement has an optional keyword OID, which associates a user-specified object identifier (OID) with the type definition. It should be used by anyone who creates an object type that will be used in more than one database."
View 4 Replies
View Related
Jun 26, 2010
I have a question about referencing simultaneously multiple tables and I have the following tables
CREATE TABLE TEAM(team_id VARCHAR2(20), ...);
CREATE TABLE AGENT(agent_id VARCHAR2(20), ...);
(I put ... because there are a lot of columns which are not interesting for this question.)
Remark: team_id and agent_id are neither primary keys nor UNIQUE.
In a third table I have a column with exactly the same data type, that is, VARCHAR2(20). What I need is to make sure that its value is at least in one of the tables TEAM or AGENT (so the only thing that is important is to check that the specified value exists no matter in which of the two tables, but just to make sure that it exists somewhere).
The solution that I have found so far, is to create a BEFORE INSERT trigger on the table. Within the trigger I create a cursor
CURSOR c IS
SELECT team_id FROM TEAM
UNION
SELECT agent_id FROM AGENT;
And then within a loop I check the value.
View 1 Replies
View Related
Oct 3, 2010
I am getting an error "Single row query returns more than one row" in an Exception block.
But in subqueries I am using IN operator not using =.
And I don't have duplicates rows in tables.
View 16 Replies
View Related
Oct 29, 2013
I want to get any employee name of deptno 10 but total count of number of employees under dept 10.
DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;
View 7 Replies
View Related
Mar 12, 2013
this is my sql=
" SELECT
hpn.id AS id,
hpn.psn_id,
hpn.last_name,
[Code].....
"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,
but it returns error single row query returns more than one row, because one people can have more that one card_number.
View 9 Replies
View Related
Oct 5, 2012
i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.
ID Course
103812 CFH
102968 REP
103812 DFH
102968 DFH
103071 DFH
102968 CFH
View 5 Replies
View Related
Apr 19, 2013
I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.
I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'
Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:
PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..
So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?
I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?
For reference, here is the query.
update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')
View 5 Replies
View Related
Sep 19, 2013
write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.
View 6 Replies
View Related
Jul 7, 2011
I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.
I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..
like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')
View 3 Replies
View Related
Jul 26, 2012
I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.
FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER)
RETURN VARCHAR2 IS
v_act_shows booking_wk_screen.act_shows%TYPE;
v_expected_shows booking_wk_screen.expected_shows%TYPE;
v_return VARCHAR2(50);
BEGIN
SELECT NVL(act_shows, 1), NVL(expected_shows, 1)
INTO v_act_shows, v_expected_shows
FROM booking_wk_screen
WHERE booking_wk_id = i_booking_wk_id
[code]...
Is there anyway through which we can achieve this in Oracle 10g.
View 16 Replies
View Related
Aug 21, 2013
i am trying to update below statement that has multiple rows but giving the error like :
update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))
error:- single return sub query return more value.
how to write update join query base on multi[ple ow.
View 8 Replies
View Related
Jun 8, 2010
In scott/tiger user total 30 view tables are stored.Right now i want to drop all views with using query.
View 3 Replies
View Related
Jul 8, 2011
i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.
select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description
[code]...
i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)
the second query was like:
SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,
[code]...
View 1 Replies
View Related
Jun 7, 2012
I have three tables as shown in the image. Need to get the details of each employee in a single row..
EMployee_id BaseSalary Bonus Hike shares
View 4 Replies
View Related
Nov 22, 2011
How to update two tables in single set or single query ?
View 8 Replies
View Related
Apr 10, 2012
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
View 8 Replies
View Related
Sep 26, 2012
User table
id | name
----------
2 | Harry
3 | Mary
Course_User table
summer_course_completed | winter_course_completed | user_id | attendance
------------------------------------------------------------------------
y | n | 2 | 20
y | n | 2 | 40
y | y | 2 | 30
n | n | 3 | 20
n | y | 3 | 60
I wish to list each student's name with the number of summer courses he has completed and the number of winter courses he has completed. I am trying this :
select u.name, count(*)
from user u, course_user cu
where u.id=cu.user_id and cu.summer_course_completed = 'y'
group by u.id;
but I can get only the number of summer courses OR the number of winter courses that each student has completed, but never both simultaneously, through a single query. Is there a way to do that ?
View 7 Replies
View Related
Sep 6, 2012
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
View 3 Replies
View Related
Sep 24, 2012
I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
View 7 Replies
View Related
