is here:
--
create table ATTILA_1 (m1 varchar2(10));
insert into attila_1 values('a');
insert into attila_1 values('a');
insert into attila_1 values('b');
SELECT
b.*
FROM
dual left outer JOIN ATTILA_1 b
ON (b.m1='c');
---
It returns correctly (with 2 or 1 rows
into attila_1 values('a');
insert into attila_1 values('a');
insert into attila_1 values('b');
SELECT
b.*
FROM
dual left outer JOIN ATTILA_1 b
ON (b.m1='c');
---
It returns correctly (with 2 or 1 rows) if the
string in the condition is 'a' or 'b', but returns
with no row (I'd like
, there is always a row.
Finally I solved this problem with a workaround:
-
SELECT
b.*
FROM
dual left outer join ATTILA_1 b
ON ((b.m1=dummy or 1=1 )and b.m1='c')
-
So I realized, if there is no column from the left side in the join-expression, the
select will return
of whether the value is there in the other tables or
not(outer-join of course)
I wrote this query in Oracle and it works
SQL SELECT a1, b1, c1, d1
2 FROM t1, t2, t3, t4
3 WHERE t1.a = t2.b(+)
4 ANDt2.b = t3.c(+)
5 ANDt1.a2 = t4.d
6 /
A1 B1
Please read INSERT INTO t4 VALUES ('A', 'DD'); as INSERT INTO t4 VALUES
('', 'DD'); in my previous mail.
Regards
Naveen
-Original Message-
From: Naveen Nahata
Sent: Wednesday, June 25, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Outer join in SQL server
INTO t4 VALUES ('A', 'DD'); as INSERT INTO t4 VALUES
('', 'DD'); in my previous mail.
Regards
Naveen
-Original Message-
From: Naveen Nahata
Sent: Wednesday, June 25, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Outer join in SQL server - A very simple question
tables or
not(outer-join of course)
I wrote this query in Oracle and it works
SQL SELECT a1, b1, c1, d1
2 FROM t1, t2, t3, t4
3 WHERE t1.a = t2.b(+)
4 ANDt2.b = t3.c(+)
5 ANDt1.a2 = t4.d
6 /
A1 B1 C1 D1
syntax. See, e.g.,
http://www.nyoug.org/9ijoin.pdf
The way I remember whether to do a left or a right outer join is I picture
an arrow pointing to the table I want 'preserved' (that is, the one I want
to see rows from even where there's no corresponding row in the other
table).
HTH,
-Roy
Roy
('', 'DD'); in my previous mail.
Regards
Naveen
-Original Message-
From: Naveen Nahata
Sent: Wednesday, June 25, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L
Subject: Outer join in SQL server - A very simple question
Sorry for the SQL Server post, working on SQL
To: Multiple recipients of list ORACLE-L
Subject: Re: Outer join in SQL server - A very simple question
I don't know the answer but can suggest some resources:
www.sqlmag.com
www.sqlpass.org/forums
www.sqlservercentral.com/forum
Just to be sure, I went to each of these this morning to make
not have an ITEM_C record which is perfectly acceptable. But for the
purposes of this report, I need to show all 601 records even if there is
no ITEM_C record for a particular item number. I know there is an outer
join somewhere. And I'm also almost sure that this SQL statement has
been written
problem - outer join where?
Hi,
I'm facing a bit of an struggle with this SQL statement. The one below
results in the correct number of records (601). However, when
I add the
tables ITEM_C (Customer Part Number) using CUST_NUM and ITEM_NUM I get
fewer records (526). The reason is because
Hello, List:
I'm doing a outer join with SUBSTR, but can't get it to work. Is there a way to get correct or get around it? Thanks in advance
SELECT rtrim(komp.inventar), rtrim(komp.typ), rtrim(komp.herst), rtrim(sysstand.so_nr), rtrim(person.vname), rtrim(person.nname), rtrim(lisa.assetnumber
Title: RE: Try Again: Outer Join with SUBSTR
SQL select * from t ;
V
ABCD
WXYZ
SQL select * from t2 ;
V
--
ABQCDR
WXQYRZ
SQL select a.v, b.v
2 from t a, t2 b
3 where a.v = substr (b.v(+), 1, 2) || substr (b.v(+), 4, 2) ;
V V
--
ABCD ABQCDR
WXYZ
-Original
In what way is it not working ?
Are you getting an error message, or not
getting the results you expect.
In the latter case, this may be because
you are cancelling the outer join by not
echoing it through all the necessary
points in the WHERE clause.
e.g.
(substr(pcmacaddress,10,2
Hi,
In our employee's attendance application the attendance data is stored in
table 'A' including employee ID and attendance date time. Another table
'B' has one column with dates of current year (365 records). For the sake of
simplicity, other fields are ignored.
Table: A
Employee_ID
Aleem,
Your query should be:
SELECT year_date, to_char(attend_date, 'hh:mi:ss' )
FROM A, B
WHERE to_char(year_date, 'dd-mm-')
= to_char(attend_date,'dd-mm-') (+)
The above says to return all records from the year_date table and , oh, by
the way, return any
:
ate.ny.us Subject: RE: Problem with Outer Join
Sent by:
[EMAIL PROTECTED
:
ate.ny.us Subject: RE: Problem with
Outer Join
Sent by:
[EMAIL PROTECTED]
08/22/2002 08:58
AM
Please respond
to ORACLE-L
Hi,
Please can anyone let me know on how to deal with the problem where you
want to outer join a table to more than 1 tables, at the moment i get error
,
ORA-01417: a table may be outer joined to at most one other table. Is there
another way the results can be achieved?
cheers
--
Please see
Use inline views:
Select *
from ( select *
from table1 , table2
where
put here outer join predicates) view1 , table3
where
put here outer join predicates
Waleed
-Original Message-
Sent: Thursday, March 14, 2002 6:33 AM
To: Multiple recipients of list ORACLE
let me know on how to deal with the problem where you
want to outer join a table to more than 1 tables, at the moment i get error
,
ORA-01417: a table may be outer joined to at most one other table. Is there
another way the results can be achieved?
cheers
--
Please see the official ORACLE-L FAQ
Hi,
im trying to create a view comprising of about 10 tables, trying to join
them together.i need a few outer jojns, but i have been told this is not
possible, i.e. having numerous outer joins in the where clause..
anyone got any info on these rules for outer joins?
cheers
--
Please see the
i need a few outer jojns, but i have been told this is not
possible, i.e. having numerous outer joins in the where clause..
Totally false. You can do anything you want in a view. If you can write it
in straight SQL, you can put it in a view.
As to how well it will perform, is another question
is to
provide some
guidelines
on how to
use the outer join
facility
On multi-table join only one table could be involved in outer join.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 12, 2002 8:23 AM
Hi,
im trying to create a view comprising of about
but you can not write:
select tb1.col_1
from table1 tb1, table2 tb2, table3 tb3
where tb1.some_col = some value
and tb2.col_a(+) = tb1.col_a
and tb2.col_b(+) = tb3.col_b
It depends how you need to outer-join the tables
But last time I used outer joins it was in I believe Oracle 8.0.5
Maybe something
]
@perceptron.ccc:
om Subject: Re: outer join
Sent by: root
This sql doesn't give me the additional rows I'm expecting.. Is this because of the
sum and group by expressions? Is there a way around this behaviour?
Oracle 8.1.6
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a,
Steve,
Generally, Sum and Group by functions do not prevent data from being
returned.
I'm not sure what you mean by not giving you the rows you expect. I would
look closely at the where clause to be sure you are selecting all the
records you want to get.
Select the rows without the group by
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd
AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between
I want to show all the available leave types in table a even if there are no records
in table l
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
Brian and all, thanks for the help, here is the sql that does what I wanted to do!
This gives me all the leave types in table 'a' and either a sum or null for values
from table 'b'. Thanks again!
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM
Title: Need Good OUTER Join
Hello all,
I'm having trouble figuring out the proper Outer join (duplicate titles) for the following:
I need a list of all the page_id (id's) and page_title (titles) from PAGES along with the security_id from USERS_X_PAGES where the user exists. USERS_X_PAGES
Title: RE: Need Good OUTER Join
What's wrong with the output you see?
Do you not like the results because you are seeing rows where u.user_id is != 5 ?
-Original Message-
From: Woody McKay [mailto:[EMAIL PROTECTED]]
I'm having trouble figuring out the proper Outer join (duplicate
Title: RE: Need Good OUTER Join
Hello all,
I'm having trouble figuring out the proper Outer join (duplicate titles) for the following:
I need a list of all the page_id (id's) and page_title (titles) from PAGES along with the security_id from USERS_X_PAGES where the user exists
Title: RE: Need Good OUTER Join
The
problem is there are duplicate data for the page_id and
page_title.
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]]Sent: Thursday, November 15,
2001 2:11 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Need
Hi,
In one of our queries a table is participating in 3 outer joins ..but oracle
only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :
for example like :
t_recur.id_prop = t_pl_map.id_pi_template(+) and
t_discount.id_prop
,
In one of our queries a table is participating in 3 outer joins ..but
oracle only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :
for example like :
t_recur.id_prop = t_pl_map.id_pi_template(+) and
t_discount.id_prop
for?
At 09:26 AM 9/6/01 -0800, you wrote:
Hi,
In one of our queries a table is participating in 3 outer joins ..but oracle
only allows one outer join per table
how can we achieve to have more than 1 outer join for a particular table :
for example like :
t_recur.id_prop = t_pl_map.id_pi_template
Harvinder,
in this particular case, I don't see the need to perform multiple outer
joins.
why not change it to:
t_recur.id_prop = t_pl_map.id_pi_template(+) and
t_discount.id_prop = t_recur.id_prop and
t_aggregate.id_prop = t_recur.id_prop and
This allows the outer join to the t_pl_map table
Title: RE: outer join problem
Try inline views. They will solve your problem.
Lisa Koivu
I can't believe I work here.
Fairfield Resorts, Inc.
954-935-4117
-Original Message-
From: Harvinder Singh [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, September 06, 2001 1:27 PM
To: Multiple
Dear All,
Can anyone of you help me for the following.
I want to give a generalized SQL syntax for Outer Join for both oracle SQL
server. Can I use ANSI Syntax say for example
select empno, ename
from emp
join dept on emp.deptno = dept.deptno (Something like this)
Bye
Sundar
--
Please see
to give a generalized SQL syntax for Outer Join for both oracle
SQL
server. Can I use ANSI Syntax say for example
select empno, ename
from emp
join dept on emp.deptno = dept.deptno (Something like this)
Bye
Sundar
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author
://MetalDrums.org
Wednesday, July 25, 2001, 8:35:24 AM, you wrote:
RS Dear All,
RS Can anyone of you help me for the following.
RS I want to give a generalized SQL syntax for Outer Join for both oracle SQL
RS server. Can I use ANSI Syntax say for example
RS select empno, ename
RS from emp
RS
45 matches
Mail list logo
