Basic Join Not Working?
Apr 20, 2008
am not very experienced with SQL. I have two tables:
Class
-----
ID (int)
Teacher (nvarchar)
Subject (nvarchar)
Student
--------
ID (int)
ClassID (int)
Name (nvarchar)
I am trying to write a SQL query that will give me the total number of students in each class. I have tried the following:
SELECT
c.ID,
c.Teacher,
c.Subject
COUNT(s.ID)
FROM
Class c LEFT OUTER JOINT ON Student s c.ID=s.ClassID
Unfortunately, that gives me an error of:Column 'Class.Teacher' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm afraid if I place everything in a GROUP BY clause, I will get some invalid results.
View 4 Replies
ADVERTISEMENT
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 Replies
View Related
May 20, 2010
I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.
Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.
On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.
View 4 Replies
View Related
Nov 29, 2011
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Here's a simplified version of the query I am having problems with:
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)
Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.
In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)
I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?
View 7 Replies
View Related
May 24, 2011
I have problems with the query below. Left join and right join is working. Well actually it's not working correct I think but that's another story wich I will leave for another posting. But with full join I am getting error ORA-00932.The columns starting with id_ are of number datatype and the ones starting with dat_ are of type date.Oracle points out the last line to be faulty. If I make that line a comment it points out the preceeding line and so on.
SQL> /
and trunc(t.dat_trans) <= to_date(:sql_dat_avst_tom,'yyyymmdd')
*
ERROR at line 24:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
select k.id_pers k_id_pers, k.dat_avst
,t.id_pers t_id_pers, t.id_trans, t.dat_trans
from (
select ka.id_pers, ka.dat_avst, ka.dat_nasta_avst
[code]...
View 29 Replies
View Related
Aug 16, 2010
I have two tables. i need to join the tables. The query is -
select v.c_venditore,v.s_venditore,v.t_diretto_indiretto,v.d_disattivazione,d.s_direzione from VENDITORE v,DIREZIONE d
where v.p_direzione=d.p_direzione order by v.s_venditore
In the table VENDITORE there are 2919 rows. I need to display all the rows. But the joining column p_direzione has some null values.I need to display the null also. But to join the two tables this is the only condition. How can i display all the rows.
View 3 Replies
View Related
Jul 27, 2010
Basic difference between oracle 10g and oracle 11g
View 3 Replies
View Related
Jul 13, 2011
I have only 9i OCP certification. What is the basic difference between the RAC and GRID.
Also I want to upgrade myself to Oracle 11g. Do I have to go thru 10g concepts to understand 11g.
View 6 Replies
View Related
Sep 26, 2013
Any info source (a book, or other) for learning how to use Visual Basic 2010 to read/update an Oracle database schema?
View 2 Replies
View Related
Oct 30, 2012
my issue in oracle database 11g.
1. I installed Oracle 11g Personal Edition, Release 11.2.0.10, 64bits,
2. Test connection(oracle in oradb11g_home1) is successed in data source, odbc,
2. Then, I tried to connect this with Visual Basic(Visual Studio 2010), data connections, add connections, .net framework data provider for oracle,
3. but I received an error message as "Attempt to load Oracle client libraries threw BadImageFormatException."
4. Then, I download the ODAC(11.2.0.3.0) for 64bits, and installed,
5. I tried to test the connection, but failed with error message as stated above,
"Unable to connect SQLState=08004 Oracle ODBC Ora-12154: TNS:could not resolve the connect identifier specified"
I need to install ODAC for 11g Personal Edition ?
View 36 Replies
View Related
Jun 7, 2012
Does windows 7 home basic support oracle 11g?
i am getting some error during installation.
View 5 Replies
View Related
Nov 17, 2010
understanding a redo/undo concept . Refer following data
create table t(n number);
insert into t values(10);
commit;
now I update as following
update t set n=20;
As per my understanding the before image i.e. n=10 is stored in undo (to be used for rollback, transaction recovery and even in instance recover but not in media recovery) and after image n=20 is stored in redo (to be used for various recovery purposes including media recovery in case of consistent backup).
So it is redo logs for rolling forward and undo for rolling back making transaction, db consistent . If my above understanding is true then what is meant by the term 'redo required for undo'?
Also, if there are 2 database db1 and db2 connected using database link where we are populating t1 table in db1 using t2 table in db2 using db link where redo and undo will be updated db1 or db2?
View 9 Replies
View Related
Oct 29, 2012
I have been tasked to look at a technology / frameworks that will allow our company to develop tools that is quick and easy to learn and easily supported. We use Oracle architecture extensively in the organization and also have APEX 4.2 installed for development purposes. We have two basic requirements or functionality that we need to implement and I and now looking at a proof of concept to determine whether we should adopt APEX or not.
1. Forms Based Tools
We have many databases with static content and users would like to have access to the data via a form. The form will allow the user to enter parameters (eg date) and on hitting a "Submit" Button, the database will be queried and a report generated and presented.
2. Diagnostics Tools
This is a bit more involved and we require a view of our static data in real time. If a specific KPI threshold is violated (eg MOC < 85%) we would like to present an alarm on a dashboard showing the alarm. For example if there are 5 entities that MOC < 85% we would like to show a "red" indicator. If no entities violate a threshold and are above MOC > 85% we want to show a green indicator. We also want a drill down function, where if the indicator is "red", a drill down will show the details of all the entities that have violated the threshold.
I have described our basic requirements, but if it can easily be done in APEX we will definitely motivate for its use.
View 1 Replies
View Related
Jul 12, 2011
I want to write one wiki page which covered basic performance Tuning(PT) details means by some command, some GUI which can be understand by engineer,QA and PS guy with less effort , obviously I am going to introduce them with all the PT labels like what is AWR, ADDM etc.
View 4 Replies
View Related
Apr 1, 2013
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 3 Replies
View Related
Oct 19, 2013
Lets say I have three tables t1 and t2 and t3.
SELECT * FROM T1;
Id
____
1
2
3
4
SELECT * FROM T2;
Id
____
1
SELECT * FROM T3;
Id
____
1
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4
One way of doing that is using the not exists clause
select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)
Is there a better way of doing this in sql ?
View 5 Replies
View Related
Aug 26, 2011
I'm looking for 3 simple things.
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
View 9 Replies
View Related
Aug 14, 2009
i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies
View Related
Oct 15, 2012
I want to create a query with only the junction of these two queries:
- select id_utilizador,nbeneficiario,nome from adm_utilizadores a WHERE exists ( select 1 from adm_util_grupos b where
a.id_utilizador = b.id_utilizador and b.id_grupo = '1') ;
- SELECT b.*,rownum as row_num FROM ADM_UTILIZADORES b ORDER BY $sidx $sord
WHERE row_num BETWEEN $start AND $end";
Virtually want id_utilizador, nome, and nbeneficiario, I can get the first query, ordered me returning the first 10.
View 3 Replies
View Related
Oct 2, 2011
I am new to sql..
I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..
I cant use group by as well, since for group by hv to select all the columns which we need to display
View 1 Replies
View Related
Jul 29, 2013
I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
View 23 Replies
View Related
Nov 13, 2011
The existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
View 5 Replies
View Related
Mar 16, 2011
im trying to update a column in the employee table with the value "YES". Im getting an error message saying im missing a SET statement from this code below:
update e
SET e.review='YES'
from employee
inner join rentals r
on e.employee_id=r.employee_id
inner join job j
on e.job_id=j.job_id
where r.plate ='FY06WNT'
and j.function !='MANAGER'
and j.function !='PRESIDENT';
View 2 Replies
View Related
Jul 7, 2010
drop table dev10 purge
/
drop table dev11 purge
/
drop table dev12 purge
/
create table dev10 as
select rownum c1, sys.dbms_random.string('U', 6) c2, trunc(sys.dbms_random.value(1, 7)) c3
from dual connect by rownum < 8
/
[Code]...
Now, Let us assume that, the record is
AAAAAA BBBBBB CCCCCC
DDDDDD EEEEEE FFFFFF
...
..
.
Now, we want dev12.c2 is 'FFFFFF' if dev11.c2 is 'BBBBBB', if we want to get this:
AAAAAA BBBBBB FFFFFF
DDDDDD EEEEEE FFFFFF
...
..
.
We can make this for SqlServer by coding:
UPDATE dev10
SET c3 = dev12.c1 FROM dev10 INNER JOIN dev11 ON dev11.c3 = dev10.c1 CROSS JOIN dev12
WHERE (dev11.c2 LIKE 'BBBBBB')
AND (dev12.c2 LIKE 'FFFFFF')
/
but, Oracle, what should we do new?
View 11 Replies
View Related
Apr 13, 2011
This is my working query in ms access...
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..
translate this query to work on oracle?
View 5 Replies
View Related
Apr 11, 2012
Equi join (Inner join)
It is the simplest join or inner. An equijoin combines rows that have equivalent values for the specified columns.
SQL> select * from x;
NAME EMAIL EMPID
Sam email@removed 1060
Rose email@removed 1061
[code]....
don't consider above mentioned queries I got valuable outputs.
NAMEEMAIL EMPID NAME EMAIL EMPID
samemail@removed 1060 sam email@removed 1060
roseemail@removed 1061 rose email@removed 1061
sonaemail@removed 1062 sona email@removed 1062
Inner join shows matches only when they exist in both tables.so , i got records 1060,1061,1062
// Referencing columns used in a USING clause.
SQL> select x.name,x.email,x.empid from x
2 inner join y
3 using (empid);
select x.name,x.email,x.empid from x
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
so query rewritten as
SQL> select x.name,x.email,empid from x
2 inner join y
3 using (empid);
NAME EMAIL EMPID
--------------- --------------- ----------
sam email@removed 1060
rose email@removed 1061
chris email@removed 1062
I mean see two different outputs.first output records twice displayed ... Yes i agree that is Inner join.second output records not displayed twice... common records only displayed once ,in x and y.
I think should n't use a table name or alias when referencing columns used in a USING clause... am i right ????
I want to know both are inner joins .how Oracle is determined both outputs ?
View 13 Replies
View Related
Jul 12, 2012
what is Hash join?how it is different from inner join?what is the sign used for inner join?(eg: like the (+) sign used for outer join)?
View 1 Replies
View Related
Jan 13, 2013
If i have a table T1 and a table T2. Table T1 is having 100 rows and table T2 is having 20 rows. When performing a Hash join ,which table should be used to make the hash table ,the larger one or the smaller one and why ?IF the data set is too small for considerations then please conser table T1 with 10 million of rows and table T2 with 1 million of rows.
View 11 Replies
View Related
Nov 1, 2010
I have two table in different instance .
IMEI in instance A
RCA_SMART_CARD in instance B
Below is the desc table :
SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)
If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
View 1 Replies
View Related
Jul 21, 2011
I have 2 sql's statement below, and just wondering if their is difference between the two sql's.
FIELDS data type:
--------------------
a.field is DATE
b.field is also a DATE
SQL1:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field = b.field
WHERE a.field between b.field AND b.field + 2
;
SQL2:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field between b.field AND b.field + 2
;
OR
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field >= b.field AND
a.field <= (b.field + 2)
;
which ever is correct between the two sql.
QUESTION: would be the two sql's generate same result set.
View 1 Replies
View Related
