SQL & PL/SQL :: Referencing To Multiple Tables Simultaneously
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
ADVERTISEMENT
Jun 12, 2013
I am trying to insert data in three tables from three csv files simultaneously. This is what I have so far:
---insert all data from three csv files
DECLARE
--zenobject
F UTL_FILE.FILE_TYPE;
[Code]....
View 5 Replies
View Related
Jul 9, 2012
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
View 6 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
Apr 22, 2013
Oracle procedure can run simultaneously?
Here is my concern, my procedure is already running manually and as per schedule time the same procedure will call by other application in the same time, So now procedure can run simultaneously or it will show any error message?
View 3 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
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
View Related
Oct 8, 2010
I have created a form in which I have 2 canvases - content canvas and a tabbed canvas.
I have only 4 fields on the content canvas and the remaining on the tabbed canvas. My requirement is such that the tabbed canvas should appear below the content canvas when I run the form, i.e. the fields on the content canvas will be visible, and below them will be the tabbed canvas.
For the content canvas, I have set the viewportX and viewport Y position as (0,0), and for the tabbed canvas, I have set the viewportX and viewportY position as (0,100). However when I run the form, I only see the tabbed canvas and the area at the top where I should see the contents from the content canvas appear blank.
View 2 Replies
View Related
May 6, 2010
I have problem viewing 2 procedures at the same time... e.g. i open procedure A, edit it, not close it. then i open procedure B from the left pane, and then i cannot see the procedure A... If i look at the File Menu on top left, it has the option of SAVE ALL, and not just SAVE.. that means both the procedures are open. but then why am i not able to see them?
View 6 Replies
View Related
May 4, 2011
--here's my set up
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
[Code] .....
my desired outcome for user 1
user1> select * from schemaowner.user_data;
user_id column1
1 USER1
my desired outcome for user2 are as follow:
user1> select * from schemaowner.user_data;
user_id column2
2 TESTER 2
the nearest solution is from with reference to [URL]
Quote:
Adding Policies for Column-Level VPD
....
SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;
the database returns a subset of rows as follows:
ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950
so how do I implement roll level and column level simultaneously?
View 3 Replies
View Related
Feb 21, 2010
I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
View 4 Replies
View Related
Sep 30, 2010
Video Rental Shop
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
View 18 Replies
View Related
Jan 19, 2011
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
View 1 Replies
View Related
Mar 2, 2011
I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.
For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.
I am pasting the test data below:
create table employee (eno number(9), ename varchar2(40));
insert into employee values(1,'Jack');
insert into employee values(2,'Jim');
[Code]....
I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.
Is there any better way to write this query in terms of performance?
View 8 Replies
View Related
Mar 29, 2011
Here is what I'm trying to do:
I have three tables:
TABLE1 TABLE2 TABLE3
NCI 1AB OPEN
NCI (NULL) (NULL)
INV (NULL) (NULL)
NCI 2AB REOPEN
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU",
l.loc AS "LOC",
l.qty AS "QTY",
o.ncikey AS "NCI",
r.description AS "NCI DESC",
o.qtyexpected AS "NCI QTY EXP",
o.qtyreceived AS "NCI QTY REC",
o.loc AS "NCI LOC",
o.status
[Code]...
View 7 Replies
View Related
Feb 11, 2011
What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies
View Related
Feb 12, 2012
Can I use MERGE statement to insert the same thing into multiple tables?
Like below--
Merge into tbl1
using tbl2
when matched then update...
when not matched then insert all.
View 6 Replies
View Related
Jan 19, 2011
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
View 2 Replies
View Related
May 1, 2013
I need to set up a trigger to check and enforce that the age of an employee at hire date is older than 18 when a new record is inserted. The age is on the PERSON table, and the hire date is on the EMPLOYEE table.
My attempt:
CREATE TRIGGER AGE_HIRE
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF FLOOR EXTRACT (YEAR FROM (PERSON.BIRTHDATE - EMPLOYEE.HIREDATE)) > 18
THEN DROP NEW
ELSE INSERT NEW
FROM PERSON, EMPLOYEE
END;
View 8 Replies
View Related
Apr 4, 2010
I tried to split the query into 2 parts , the outcome is fine but when ever i merge them ( include other table fields ) . The result is 3X3 rows .. it should be just 3 rows
The First Query ( Is running fine , 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......
The Second Query ( Is running fine also, 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WP.DESCRIPTION,
WP.LINECOST
FROM WORKORDER , WPSERVICE WP
WHERE PARENT IS NULL
AND WORKORDER.WONUM = WP.WONUM
AND WORKORDER.WONUM = :WO
The problem occurs when i try to merge both these queries , as the result shows 9 rows ..
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......
View 3 Replies
View Related
Mar 29, 2011
I have two tables containing dates:
A
From____________To________
01.01.2009 || 01.01.2010
01.01.2013 || 01.10.2014
B
From____________To________
01.01.2007 || 01.01.2008
01.01.2011 || 01.10.2012
01.01.2009 || 01.01.2010
01.01.2015 || 01.01.2016
I need to get list of all entries from Table A and entries from B where dates are before min begin date from A or somewhere between any entry from A.
In this Example result:
From____________To________
01.01.2007 || 01.01.2008
01.01.2009 || 01.01.2010
01.01.2011 || 01.10.2012
01.01.2013 || 01.10.2014
View 1 Replies
View Related
Jun 21, 2012
started using APEX recently and am trying to configure the APEX Listener in standalone mode to listen to two databases simultaneously. Is it possible?
View 2 Replies
View Related
Aug 9, 2012
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
I am using oracle RAC 11g R2
View 5 Replies
View Related
Aug 25, 2011
I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:
Table1
code date name
aaaa 1/1/2003 billy bob
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
dddd 4/4/2006 mary little
eeee 5/5/2007 joe black
Table2
code date name
aaaa 2/2/2004 larry cole
aaaa 3/3/2005 nat king
bbbb 2/2/2004 louis lewis
cccc 3/3/2005 joe crab
cccc 6/6/2008 dennis jackson
dddd 7/7/2009 missy muffet
dddd 5/5/2007 joe black
eeee 8/8/2010 elton rocket
desired output results from select
aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole
aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king
cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab
cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet
eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket
Here is the select that I have so far:
select table1.rowid, table1_code, table1_date, table1_name,
table2.rowid, table2_code, table2_date, table2_name from table1, table2
where table1_code= table2_code
order by table1_code;
The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping.
View 1 Replies
View Related
Mar 25, 2011
I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
(field.table)
CustName.CUST
SalesNo.SALE
SalesDate.SALE
ItemDes.ITEM
Qty.SALEITEM
OrderComplete.SALEITEM
with 2 types of WHERE criteria:
WHERE SalesDate is between 'dateX' AND 'dateY'
and also WHERE OrderComplete = 'Y'
i understand this will require some sort of join in the statement so the keys for the different tables are as follows:
CUST
CustNo - PK
SALE
SalesNo - PK
CustNo - fk
ITEM
ItemNo - PK
SALEITEM
SalesNo -fk
ItemNo - fk (compound PK)
i have had a play around with using some joins & embedded statements
View 4 Replies
View Related
Oct 8, 2010
I've been given the task of importing an XML file into multiple tables within our database using PL / SQL and I am wondering what the best approach would be.
The files will be quite large and I need the code to be as flexible as possible.
View 1 Replies
View Related
Apr 28, 2011
Suppose a view is created using multiple tables. Can we update the view? And when we update the view will it effect the base table?
View 5 Replies
View Related
Aug 26, 2010
I am issuing an update statement in which I am using multiple tables it is giving me an error " set keyword missing"
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
set ecdnv.ContTPRecCount = 1000
where tc.default_category_id = (select primary_key from y_ojb_category where tree_position = 'CONT')
and ecdnv.detail_field_id=tc.default_category_id;
update E_CONT_DETAIL_NUMB_VALUE ecdnv, y_obj_category yoc, t_contact tc
*
ERROR at line 1:
ORA-00971: missing SET keyword
View 4 Replies
View Related
