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.
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 )
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 ..
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.
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.
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?
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.
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;
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.
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.
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
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
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?
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
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.
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?
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:
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
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.
