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
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...!
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number FROM employee_phones
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
SELECT * FROM (SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
phone_number ------------------- 1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = export.emp_id ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
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.
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.
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.
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.
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.
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.
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.
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.
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.
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query: SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
SELECT DISTINCT PATIENT_ID , ( SELECT ROWNUM,APPOINTMENT_ID FROM AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2 AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
[code]...
MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.
SELECT DISTINCT PATIENT_ID , ( SELECT TOP 1 APPOINTMENT_ID FROM EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2 AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2 AND ADH.VISIT_TYPE_ID NOT IN (7)
We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query: SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
