SQL & PL/SQL :: Select Data From All Tables At Once
Sep 12, 2011Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies
ADVERTISEMENT
SQL & PL/SQL :: Select Data From 2 Tables And Insert Into Another Table
Apr 25, 2013I want to select data from different tables and insert this into one table based on some conditions:
SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1
[Code]....
So I tried doing this with selecting the data and looping through it to do the insert.
DECLARE
--
-- WELLS
--
CURSOR c_well
[code].....
3 Tables In SELECT
Jul 22, 2010When I use the below code in my perl script (it is oracle database):
$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
everything is ok... and when I try to add a third table...
$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!
Maintain Large Tables / Cleanup Data From Our Tables
May 18, 2011I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
Implementation Where Data From DB2 Tables Are Moved To Oracle Tables
Sep 3, 2012I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedHow To Select Rows From Two Tables
Aug 30, 2007Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
Select Query From Two Tables?
Jun 7, 2011I have two tables a and b column names are id and date. Data type of date in a and b are different
table (a)
date id
10-DEC-01 2:08:39 PM 1
10-DEC-01 2:08:39 PM 2
10-JAN-02 10:10:22 PM 3
10-JAN-02 10:10:22 PM 4
10-JAN-02 10:10:22 PM 5
table (b)
date id
10-DEC-01 1
10-DEC-01 2
I need table b like this one
table (b)
date id
10-DEC-01 1
10-DEC-01 2
10-JAN-02 3
10-JAN-02 4
10-JAN-02 5
SQL & PL/SQL :: Select Count Using 2 Tables?
Mar 28, 2011I'm having trouble with some SQL code regarding count and an outer join.
Here is my code.
SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;
Where salesperson is a salesperson table and ord is a table containing orders.
The orders table contains a FK to salespersonid in the salesperson table.
I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.
SQL & PL/SQL :: Select From Multiple Tables?
Mar 29, 2011I 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
Coding Select In Multiple Tables
Aug 25, 2011I 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.
Select Fields From Across Multiple Tables?
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
SQL & PL/SQL :: Give Select Grant For Few Tables?
Jul 8, 2010I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.
View 9 Replies View RelatedSQL & PL/SQL :: Select * From One Table But Not All Tables In A Query
Oct 30, 2013How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
SQL & PL/SQL :: Select Records Where Date Is Max From Tables
Oct 6, 2011 I've got 3 tables and I need to select records where date is max from these tables.
select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438
how to do that ?
Application Express :: Select From Several Tables
Nov 26, 2012i have 3 tables in my database..
table1 with one column
table1
table1_id
11
12
13
14
table2 with one column
table2
table2_id
21
22
23
24
table 3 with 2 primary foreign keys from table1 and table2
table3
table1_id table2_id
11 21
11 22
12 23
12 24
13 21
14 22
i want a select statement that selects all from table2 where the specified table1_id is not in the list of the table1_id in table3
for example if i want the list for id 11 it should return (23, 24)
i came up with the following but it didn't work
select table2_id
from tabel3 c, table2 b
where table1_id <> 11 and c.table2_id = b.table2_id
i know my explanation is not that clear..
How To Grant Select On All Tables Of User1 To Another Schema
Jan 18, 2012User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.
I dont want user2 to have "select any table" privillege.
User2 should not have "drop" privillege on his own tables.
Long Select Operate On 5 Tables - Optimization?
Sep 4, 2013I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
Left Joining Select Of Multiple Tables
Nov 22, 2010i 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.
Select From Two Tables - Remove Repeated Rows
May 31, 2011I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
Making A Select Statement To Join A Few Tables Together?
May 2, 2009I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.
I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example
SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';
SQL & PL/SQL :: Select Columns From Different Tables Dynamically In A Function
Jan 25, 2013im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
View 2 Replies View RelatedGet All Tables That A User Can Select / Insert / Update Or Delete
Dec 17, 2010How to get all the name of tables that a user can select, insert, update or delete?
View 2 Replies View RelatedSQL & PL/SQL :: Cursor Select For Update / Multiple Columns Of Different Tables
Apr 8, 2010i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.
SQL & PL/SQL :: Select Columns Of 3 Tables In Such A Way That Period Column Should Be In Group By Function
Aug 16, 2011i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
Oracle Data Pump - Export Data From Schemas Or Tables
Oct 11, 2012I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies View RelatedData Warehouse - How To Insert / Access Data In The Tables
May 30, 2011I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies View RelatedSQL & PL/SQL :: Select The Valid Data From VARCHAR2 Data
Sep 23, 2011I need the output like...
OUTPUT(MM/DD/YYYY): 2/03/2011
02/03/2011
create table test (trans_date varchar2(1000));
Insert into test values ('2/03/2011')
Insert into test values ('02/03/2011')
Insert into test values ('12/33/2011')
Insert into test values ('xxx')
Insert into test values ('33/33/2011')
Insert into test values ('03/03/11')
Cannot Select Data
Nov 27, 2012In SQLDeveloper (I use oracle 11g), I can see a table with its all data. However, in sqldevelopr, I cannot select some data of it.
If I use sqlplus, it works fine.
Why I cannot select some data of that table in SQLDeveloper?
Retrieving Data From 3 Or More Tables
Apr 12, 2008I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
Inserting Data Into Two Tables
Feb 15, 2009I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables