Natural Join With ON Clause?

Apr 22, 2012

CODESQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800

SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)

CODESQL> select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;
on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

[code]...

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this
SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inner Join Used Using Clause?

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

Inner Join On Clause Statement?

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

SQL & PL/SQL :: Join Syntax In From Clause

Jul 21, 2010

I have this ORACLE SQL and just about understand join syntax in the From clause, i.e.

SELECT *
FROM TABLE 1 LEFT OUTER JOIN TABLE 2
ON TABLE1.FIELD_X = TABLE2.FIELD_X

However, I've inherited the sql below.

SELECT
RELOCATION.START_DATE,
STUDENT.SURNAME,
STUDENT.FORENAME
CURRENT_SCHOOL.BASE_ID
RELOCATIONS.STUD_ID
[code]......

I particularly don't understand this part

' FROM (MYDATABASE.STUDENT STUDENT
LEFT OUTER JOIN MYDATABASE.BASES CURRENT_SCHOOL '

why the table name student is referenced twice?And again for ' MYDATABASE.BASES CURRENT SCHOOL '?

When I put this into SSRS it shows only links between the tables STUDENT, RELCOATIONS and CURRENT_SCHOOL. Bases isn't mentioned in the tables diagram. it is still referred to in the raw SQL.

The above SQL works fine, i just don't understand what it's doing!

View 3 Replies View Related

SQL & PL/SQL :: Join Update Clause

Jun 9, 2010

I want to use join condition in update syntax.Like the following way but it doesnot work.how to fix it.

update tab_1 a
set a.qty = b.sell_qty
from tab_2 b
where a.nbr=b.nbr

View 3 Replies View Related

PL/SQL :: Difference - In Conditions (Join And Where Clause)

Sep 19, 2013

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause. I tried both way and found same results. Even in Statistics Plan not much differences. 

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl  On Gl.Location_Id = I.Location_Id   And Gl.Location_Id In ( 1767, 1747,202,1625)    Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0  And I.Condition_Id != 325         

2. Here I am using location filter in Where clause

SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl   On Gl.Location_Id = I.Location_Id   Inner Join Sc1.Condition C        On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0       and I.LOCATION_ID in ( 1767, 1747,202,1625)    And I.Condition_Id != 325.

View 23 Replies View Related

SQL & PL/SQL :: Difference Between Primary And Natural Key

Jul 5, 2011

what is the difference between primary key and natural key ?

View 3 Replies View Related

SQL & PL/SQL :: Normal Join And Outer Join

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

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 Replies View Related

SQL & PL/SQL :: Left Outer Join Versus Right Outer Join

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

SQL & PL/SQL :: Using 1=1 In Where Clause?

Nov 9, 2011

what is the use of 1=1 in the where clause?

View 10 Replies View Related

SQL & PL/SQL :: How To Join Two Where

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

Import With Where Clause?

Jul 30, 2010

I have a exp .dmp file of a database that has over 1000 tables. I would like to import all tables that don't begin with 'C'. Is this possible with IMP (without listing each table I want)?

View 1 Replies View Related

SQL & PL/SQL :: Hierarchical And With Clause

Jul 31, 2013

>select level ,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null;

Output:-
>
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698

LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
2 7782 CLARK 7839
3 7934 MILLER 7782

Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.

View 4 Replies View Related

SQL & PL/SQL :: Decode In Where Clause

Aug 3, 2013

I have a scenario where in report table I need to get

IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.

I tried using decode in WHERE CLAUSE , but not successful.

Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.

Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.

Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.

Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...

View 14 Replies View Related

SQL & PL/SQL :: Group By Clause

Mar 31, 2011

This time, I am going to provide the DML statements.

I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.

the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :

2001 EMPL TRAINING-MIS 20
2002 OTHER PROF SERV-HR 40

The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?

SELECT ACCT_NUMBER, SUM(BALANCE)
FROM TEST
GROUP BY ACCT_NUMBER
CREATE TABLE "TEST"
("ACCT_NUMBER" VARCHAR2(20 BYTE),
"DESCRIPTION" VARCHAR2(20 BYTE),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Where Clause In Concat

Jul 24, 2010

I has a table with team names like

Table name:- Teams

Structure:- name
Ind
Aus
Pak

I want the o/p as:- Fixtures
Ind Vs Aus
Ind Vs Pak
Aus Vs Pak
.......

I tried it but failed in what condition to use in the where clause.I did up to this:-

select name||' Vs ' || name from teams where .......;

how to get the result.

View 2 Replies View Related

SQL & PL/SQL :: WITH Clause In Oracle 11g R2

Sep 20, 2012

I am getting error 'ORA-03113: end-of-file on communication channel' in Oracle 11g R2 version when I ran the below query.

WITH tab1 AS (SELECT C_NUM as col1 FROM test123_v)
SELECT COUNT (distinct col1) num_assignments FROM tab1
UNION ALL
SELECT COUNT (distinct col1) num_assignments FROM tab1

The same query is working fine in 10g.Here test123_v is view created using table test123, which has one column c_num.When I use the table instead of view in the with clause I am getting the output in 11g. The below query gives the output.

WITH tab1 AS (SELECT C_NUM as col1 FROM test123)
SELECT COUNT (distinct col1) num_assignments FROM tab1
UNION ALL
SELECT COUNT (distinct col1) num_assignments FROM tab1

View 2 Replies View Related

SQL & PL/SQL :: Decode With Between Clause

Aug 11, 2011

I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"

select distinct record_number
from meta m, procedu b, control v, fact f, calendar dc, person p
where f.key = m.key and f.group_key = b.group_key
and b.proc_key = v.proc_key
and f.calendar_key = dc.calendar_key
and f.person_key = p.person_key
and VALUE BETWEEN DECODE((Select distinct operator_type
[code]....

But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"

View 4 Replies View Related

SQL & PL/SQL :: Order By Clause?

Mar 12, 2013

I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.

View 2 Replies View Related

SQL & PL/SQL :: From Clause With Conditions

Apr 2, 2013

You think i can do a from clause with conditions ??

The reason is that i need to retrieve fields from different schemas depending on a column in a common table

let s say the column CRITERIA_COL is in table Common
If COMMON.CRITERIA_COL has value 1 then the select query should fetch results from the schema : SCHEMA1.MY_TABLE
If COMMON.CRITERIA_COL has value 2 then the select query should fetch results from the schema : SCHEMA2.MY_TABLE
If COMMON.CRITERIA_COL has value 3 then the select query should fetch results from the schema : SCHEMA3.MY_TABLE

Something like this:

Select my_Col1, my_Col2 from
(case COMMON.CRITERIA
when '1' then SCHEMA1.MY_TABLE
when '2' then SCHEMA2.MY_TABLE
when '3' then SCHEMA3.MY_TABLE
)

but that is not working .By the way my query is not just that, it s a more complicated query, that s just the portion I am having trouble with .

View 1 Replies View Related

SQL & PL/SQL :: Sysdate In Where Clause

Apr 22, 2010

I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.

Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.

The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.

View 13 Replies View Related

SQL & PL/SQL :: Variable In The From Clause

May 8, 2013

Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.

---the tables

create table org_a (emp_id number(5) not null, name varchar2(20));
create table org_b (emp_id number(5) not null, name varchar2(20));
create table org_c (emp_id number(5) not null, name varchar2(20));

---the records

INSERT ALL
INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE')
INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE')
INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK')
INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN')
INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE')
INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND')
SELECT * FROM dual;

---verify inserts

SELECT * FROM org_a
UNION ALL
SELECT * FROM org_b
UNION ALL
SELECT * FROM org_c;

---i want the table name to be dependent on a variable. eventually,
---i intend to link v_org to a form with radio buttons (values: 1, 2, 3)
---to keep this simple, i'll just assign 1 to v_org

DECLARE

v_org number(1) := 1;
v_table varchar2(5);

BEGIN
v_table :=
CASE v_org WHEN 1 THEN 'org_a'
WHEN 2 THEN 'org_b'
WHEN 3 THEN 'org_c'
END;

SELECT * FROM v_table;

END;

--this is what i receive

SQL> /
SELECT * FROM v_table;
*
ERROR at line 10:

ORA-06550: line 10, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored

View 22 Replies View Related

SQL & PL/SQL :: In Clause Is Not Working?

Sep 18, 2013

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');

IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)

If I pass one name 'KIRAN-KUMAR' from .net It's working.

If I pass multiple names from .net query not returning any records.

View 4 Replies View Related

SQL & PL/SQL :: Dynamic WHERE Clause - Too Big

Aug 18, 2011

I have to create a WHERE clause like :

SELECT * FROM wc_claims WHERE part_nbr in l_part_nbr ;

I have three tables:
table A has - user_id, supplier
table B has - supplier, part_nbr
table C , wc_claims, has part_nbr and 78 other columns.

what i need to do is when user logs in with his user_id, he should see only that info from table C, corresponding to his supplier, and the part_nbr.

SELECT supplier from table_A WHERE user_id = 'abc' ;
supp_abc
SELECT distinct part_nbr from table_B WHERE supplier = 'supp_abc' ;
partA, partB, partC,........partZZ

SELECT * FROM wc_claims WHERE part_nbr in (partA, partB, partC,........partZZ );

I have created a function :

create or replace Function wcl_partnbr ( p_supp IN varchar2 )
RETURN CLOB
IS

[Code].....

so that I can use that in the WHERE clause, because one supplier can have thousands of parts...so it works fine, if there are 200 part numbers for one supplier, but above that.. it doesnt.

View 15 Replies View Related

SQL & PL/SQL :: Dynamic Where Clause

Jul 9, 2010

I having below table :

EXAM2@orcl> desc search_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROL NOT NULL VARCHAR2(7)
CNM NOT NULL VARCHAR2(30)
FNM NOT NULL VARCHAR2(30)
MNM NOT NULL VARCHAR2(30)
DOB NOT NULL DATE
YEAR NOT NULL NUMBER(4)
EXAM NOT NULL VARCHAR2(1)
MAIN_SUPP NOT NULL VARCHAR2(1)

In a dotnet application user is going to input/select all/any of the above column in where clause.If user has given value for rol column then ref cursor should be :

select * from search_table where rol like <given value>
endif
and/or

If user has given value for cnm column then ref cursor should be :

select * from search_table where rol like <given value> and/or cnm like <given value>
endif

I mean whatever value is being given; it should be part of where clause.For it i am going to :

EXAM2@orcl> CREATE OR REPLACE PACKAGE rollsearch AS
2 TYPE t_cursor IS REF CURSOR;
3 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
4 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
5 io_cursor IN OUT t_cursor);
6 END rollsearch;
7 /

EXAM2@orcl> CREATE OR REPLACE PACKAGE BODY rollsearch AS
2 Procedure rol_cursor(c_rol in varchar2,c_cnm in varchar2,c_fnm in varchar2,
3 c_mnm in varchar2,d_dob in date,n_year in number,c_exam in varchar2,c_main_supp in varchar2,
4 io_cursor IN OUT t_cursor)
5 IS
6 v_cursor t_cursor;
7 sql varchar2(1000);
8 BEGIN
9

What should i write further; so that i may get ref cursor as whereed clause for given value. So that i may populate my datagrid view for that web form.

View 14 Replies View Related

PL/SQL :: Use Of Commit Clause

Dec 28, 2012

I have this stored procedure and sequences:

create sequence a_seq;
create sequence b_seq maxvalue 26 cycle;
create sequence c_seq maxvalue 1000 cycle;

create or replace
procedure inserta_en_B (numregistros in integer) as
ultimo_año_nuevo date := trunc (sysdate,'year');
dias_transcurridos number(3) := sysdate - ultimo_año_nuevo;
begin
[code]........

First i insert into b 400000 rows using:

execute inserta_en_b(400000));
commit;

But then i need to insert 100000 rows more using the stored procedure and without removing the 400000 rows stored before. I think i need to use the commit clause, but i dont know where.

View 3 Replies View Related

SQL & PL/SQL :: Parameters With In Clause

Oct 17, 2011

How to how to get this sql to work?

select a.in_house_ref
from media_item a
where
((&rack is null) or a.in_house_ref in (&rack));
--a.house_media in ('CH127600','BF101042'), example values

I've tried the a.in_house_ref in (:rack) by itself and that doesn't work for the values above and hence changed it to (&rack) and that works. The issue I run into now is that the the paramater can be null, ie., no values need be entered by users and hence I put the is null statement. When I run the full statement above, oracle returns an error, i.e. , ORA-00936: missing expression.

View 7 Replies View Related

Cursor Instead Of With Clause

May 7, 2013

insert into cte
with cteas
as
(
select hit from radial,gib
where no=id
)
[code]........

i want to update the values based on with clause so i used like above.but it is time consuming so i planned to use cursor . is it effective or any other methods avail for the above. How to write cursor and use for the above ?

View 1 Replies View Related

SQL & PL/SQL :: Framing Where Clause

Jul 20, 2012

CREATE TABLE TEST
(
TEST_CASE NUMBER,
ATTRIBUTE VARCHAR2(1000 BYTE),
ATTRIBUTE_ID NUMBER,
VALUE VARCHAR2(1000 BYTE),
PERATOR VARCHAR2(1000 BYTE)
)

[Code].....

OUTPUT NEED TO BE TO STRING.

WHERE ATTRIBUTE_ID = 30640 AND VALUE IN ('A','B','C') AND ATTRIBUTE_ID = 30744 AND VALUE BETWEEN 7 AND 9
WHERE ATTRIBUTE_ID = 31110 AND VALUE IN ('100')

View 13 Replies View Related







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