SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle
Apr 8, 2012
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......
SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.
View 9 Replies
ADVERTISEMENT
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
Nov 22, 2010
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B
left join C on (C.ID_A = A.ID and C.ID_B = B.ID)
[... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
View 7 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
Oct 30, 2012
I am trying to create view ..Using totally Five tables....
The main driven table almost 10 GB Data
When i am creating a view..
End of communication channel Error raise...
At the same time I am running query ..Running Fine...
View 5 Replies
View Related
Apr 21, 2010
I have a table as shown below,
SQL> select * from Main_Table;
AA
--------------------
SUB1
SUB2
Here the SUB1 and SUB2 are "tables" and are similar in their structure. The "Main_Table" will be update dynamically and the no of rows in this table will vary.
Now my question , i need to create a view which will have all the rows from these tables ,in the current case it is something like
create or replace view sample
as
select * from SUB1 union all
select * from SUB2
How can this be achived. I tried as shown below:
spool file_to_exe.sql
select 'select * from ' || AA ||' union all ' from Main_table;
spool off
i will end up in a union all "extra" , if i do like this.
how can achieve this ?..
View 5 Replies
View Related
May 21, 2013
we create index on multiple tables in oracle
View 12 Replies
View Related
Dec 7, 2012
i have 20 tables in schema and wanted to take count using store procedure.
how to create a sp for taking count of multiple tables
View 5 Replies
View Related
May 19, 2011
can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?
View 1 Replies
View Related
Apr 26, 2013
I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .
Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).
I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.
View 11 Replies
View Related
Nov 28, 2012
using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
View 4 Replies
View Related
Feb 8, 2011
I have two tables. The first contains all Segment information within Oracle i.e;
Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001
and the second table contains financial data, but only the segment code;
Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000
what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.
Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;
SELECT
b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
[code].....
View 6 Replies
View Related
Jan 16, 2013
We have two tables tab1 and tab2 same structures and empid and id are keys.
We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.
View 11 Replies
View Related
Mar 24, 2012
I have a table called test_renames that has two columns(new_name and old_name) and one row with the following values
NEW_NAME OLD_NAME
DRUG10 DRUG1
I have another table called SH0 that has 10,000 rows and the column of interest is called ITEM1. ITEM 1 contains the following distinct drug names:
DRUG1,DRUG2,DRUG3,DRUG4,DRUG5
Each DRUG has multiple rows associated with it.
I need to only update rows for DRUG1 and set the name from DRUG1 to DRUG10 for all rows where ITEM1 is DRUG1.
However I need to write this update statement by joining with the values in the test_renames table that contains the NEW_NAME and the OLD_NAME.
DRUG1 has 2,000 rows associated with it. The following SQL statement gives me 2,000 rows
(SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG,sh0 sh
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
However when I run the following update statement, it appears that the SQL query updates all the 10,000 rows in the table instead of just touching the 2,000 rows for DRUG1. It does change all the rows for DRUG1 to DRUG10, but all the other ITEM1 values become null. What I need from the update statement is only chnage the rows for DRUG1 to DRUG10 and leave the other rows that I associated with DRUG2, DRUG3 etc. Here is my update statement.
UPDATE SH0 SH
SET ITEM1 = (SELECT NVL(CHG.NEW_NAME, SH.ITEM1)
FROM test_renames CHG
WHERE UPPER(SH.ITEM1) = UPPER(CHG.OLD_NAME)
AND UPPER(SH.ITEM1) IN (SELECT UPPER(OLD_NAME) from test_renames))
This update statement touches all 10,000 rows. I am looking to update only the relevant 2,000 rows for DRUG1.
View 2 Replies
View Related
May 20, 2010
consider the following
WITH table1 AS
(
SELECT 345 id, To_Date('1/31/2010', 'mm/dd/yyyy') dt, 123 cid, 'test' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/1/2010', 'mm/dd/yyyy') dt, 121 cid, 'test2' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/2/2010', 'mm/dd/yyyy') dt, 112 cid, 'test3' txt FROM dual UNION ALL
SELECT 345 id, To_Date('2/3/2010', 'mm/dd/yyyy') dt, 162 cid, 'test4' txt FROM dual UNION ALL
[code]..
i want to output the following:
{code}
IDDT CIDTXT
3451/31/2010123test6
3452/1/2010121test2
3452/2/2010112test3
[code]....
as you can see, i want to join both tables (table1 and ov_dates) and take dt from ov_dates table look it up in table1 and thengrap ov_dt and looking up in table1 and copy values of one row to the other.
for example, in ov_dates i have 3/31 dt and 4/7 ov dt. i will look for 3/31 and 4/7 row in table 1 and copy columns values from 4/7 to 3/31you can see that cid for 3/31 stay the same (since i dont want to replace the value) but txt value was replace with value from 4/7
another examplein ov_dates we have 1/31 dt and 2/5. so i go into table1 get 1/31 and 2/5 row and copy values from 2/5 into 1/31/ as you can see the txt column value for 1/31 becametest6 since it was taking from 2/5. the same thing for the rest ofdata
View 10 Replies
View Related
Apr 13, 2010
i have two tables
create table tab1(
ename varchar2(10),
empid number(3) primary key);
SQL> insert into tab1 values('Ram', 101);
1 row created.
SQL> insert into tab1 values('Hari', 102);
1 row created.
SQL> insert into tab1 values('Shyam', 103);
[code]....
but i want like this
EMPID CONTACT_NO(PHONENO, MOBILENO)LOCATION
---------- -----------------------------------------
101 80123456 Banglore
101 9986234567 Banglore
102 809863728 Banglore
102 9032456578 Banglore
103 409863728 Hyderabad
[code]....
i want to display the ename,contact_no,location joining the two tables.
View 1 Replies
View Related
Jun 30, 2012
What is the Best way to import data to multiple tables in Oracle Data base from sql server?
1)linked server?
2)ssis ?
If possible share me the query to done this task using Linked server?
View 2 Replies
View Related
Mar 7, 2013
What is the Best way to import data to multiple tables in Oracle Data base from sql server?
1)linked server?
2)ssis ?
If possible share me the query to done this task using Linked server?
View 1 Replies
View Related
Jun 16, 2008
We are using Oracle 10g rel 2. The replication is setup on 1 server which is in City A, and the snapshot server is in City B.
City A .
Create materialized view log on table-a
with primary key
including new values.
City B database.
Create user test_rep identified by test
grant connect, resource, create any materialized view , table, view , procedure to test_rep.
Create materialized view city-A_db_MV
refresh fast
select * from cityA.Tablea@city-Adb
When i select from city-A_db_MV, it showed the complete table-A of city-A database.
Now if we make any changes to City-A table at the Master site, will it be propagated automatically to the MV site.
I guess we need to create jobs to push / or refresh fast .. isnt it. But exactly how to do it is a question.
Secondly if we make a replication group at Master site at city-A db, how do we refresh that Group and how to monitor whether it is refreshing on time or not? do we need to see the jobs every now and then.
but still a lot of questions unanswered, even though i had read the documents earlier.
1-The MView was created without identifying that after what interval it will be fast refreshed.
2- How to Manually refresh it. Does it support On Commit, I think it is not.
3- Where should be we make a group and then add the table to that group and refresh that group.
Should this group belongs to the Master Site or to MV site?
View 1 Replies
View Related
Nov 16, 2011
I am not able to find any information regarding inserting data with timestamp format without putting the date.
This is what i want to do:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';
I create the column data type as timestamp with time zone. This is value i have inserted:
Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);
And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:
Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;
But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.
BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:
Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;
Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?
View 3 Replies
View Related
Feb 3, 2010
is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.
$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0
View 1 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
May 15, 2003
i'm working on a project right now using Oracle Forms 6.0 and Oracle 9i. after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and somehow upload the data in the text file to another database.
i will also need to create the text file for multiple records.
View 7 Replies
View Related
Jan 7, 2013
Basically, i was asked to join Table 1 and Table 2 together in oracle. I have been given the values of Table 1. For example, Depot_ID has value of DEP01, DEP02, DEP02 etc. Therefore, I have to create table 2 with those attributes given below. But the attribute name of the Table 2 is Depot_No which is different.
My question: Is it correct to give the same values of Depot_ID in Table 1 to Depot_No in Table 2, such as, DEP01, DEP02, DEP02?
How to join those two tables below:
Table 1:
Depot_ID
Address
Location
Table 2:
Product_ID
Depot_No
Quantity
Aisle
Bay
View 2 Replies
View Related
Oct 8, 2010
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies
View Related
Jun 16, 2009
I have a table similar to the following,
USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...
which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.
select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user
However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:
user, latest_home_number, latest_work_number
View 3 Replies
View Related
May 8, 2010
Table Name : Trans
chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)
where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00
I need to create the view as follows
View Name : Pattiyal
vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)
The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null
Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn
Is it possible to create a view as above
View 1 Replies
View Related
Sep 25, 2012
There is an error after error, that's why I came here
What's is wrong? error message: virtual column not allowed here
CREATE TABLE test2(
id NUMBER PRIMARY KEY,
surname VARCHAR2(20) NOT NULL);
CREATE TABLE test1(
[Code]...
It would be nice to describe this situation and where does this error come from.
View 1 Replies
View Related
Dec 1, 2011
Can we create view in another view
View 10 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
