SQL & PL/SQL :: Select Data From All Tables At Once

Sep 12, 2011

Is 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, 2013

I 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].....

View 6 Replies View Related

3 Tables In SELECT

Jul 22, 2010

When 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...!

View 1 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I 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.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I 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 Related

How To Select Rows From Two Tables

Aug 30, 2007

Consider 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

View 3 Replies View Related

Select Query From Two Tables?

Jun 7, 2011

I 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

View 1 Replies View Related

SQL & PL/SQL :: Select Count Using 2 Tables?

Mar 28, 2011

I'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.

View 13 Replies View Related

SQL & PL/SQL :: Select From Multiple Tables?

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

Coding Select In Multiple Tables

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

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

View 4 Replies View Related

SQL & PL/SQL :: Give Select Grant For Few Tables?

Jul 8, 2010

I 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 Related

SQL & PL/SQL :: Select * From One Table But Not All Tables In A Query

Oct 30, 2013

How 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

View 15 Replies View Related

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 ?

View 11 Replies View Related

Application Express :: Select From Several Tables

Nov 26, 2012

i 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..

View 1 Replies View Related

How To Grant Select On All Tables Of User1 To Another Schema

Jan 18, 2012

User1 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.

View 1 Replies View Related

Long Select Operate On 5 Tables - Optimization?

Sep 4, 2013

I 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.

View 3 Replies View Related

Left Joining Select Of Multiple Tables

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

Select From Two Tables - Remove Repeated Rows

May 31, 2011

I 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

View 2 Replies View Related

Making A Select Statement To Join A Few Tables Together?

May 2, 2009

I 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';

View 6 Replies View Related

SQL & PL/SQL :: Select Columns From Different Tables Dynamically In A Function

Jan 25, 2013

im 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 Related

Get All Tables That A User Can Select / Insert / Update Or Delete

Dec 17, 2010

How to get all the name of tables that a user can select, insert, update or delete?

View 2 Replies View Related

SQL & PL/SQL :: Cursor Select For Update / Multiple Columns Of Different Tables

Apr 8, 2010

i 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.

View 5 Replies View Related

SQL & PL/SQL :: Select Columns Of 3 Tables In Such A Way That Period Column Should Be In Group By Function

Aug 16, 2011

i 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

View 13 Replies View Related

Oracle Data Pump - Export Data From Schemas Or Tables

Oct 11, 2012

I 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 Related

Data Warehouse - How To Insert / Access Data In The Tables

May 30, 2011

I 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 Related

SQL & PL/SQL :: Select The Valid Data From VARCHAR2 Data

Sep 23, 2011

I 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')

View 4 Replies View Related

Cannot Select Data

Nov 27, 2012

In 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?

View 6 Replies View Related

Retrieving Data From 3 Or More Tables

Apr 12, 2008

I 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;

View 3 Replies View Related

Inserting Data Into Two Tables

Feb 15, 2009

I 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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved