New member / outer join problem
Hi All,
I'm a new member on this list. I'm not too familiar with ORA, but I have some
experience in other RDBMS's.
I had run into the following problem.
My task is to write a query, which returns with an empty (full of NULLs) row, if the
condition is false.
I'm thinking in outer joins.
I made a test. It returns all the matching rows (if there are such rows), but
unfortunately returns with no row, if the condition is false.
Could someone help me, what could be the problem? My oppinion is that it must be work
fine... :-)
I use Oracle9i Enterprise Edition Release 9.2.0.1.0 .
My test 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) if the string in the condition is 'a' or 'b',
but returns with no row (I'd like to see one row, with full of NULLs) if I call it
with 'c'... :-(
Could you tell me, why is this? Or any idea to solve this task?
Thanks in advance:
Attila
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: New member / outer join problem
Attila,
Review your logic. A row full of NULLs has no signification. If your statement is
embedded into either some PL/SQL code or a 3rd generation language, Oracle will
generate a 'no data found' error, and this is what you need to trap.
For instance in PL/SQL
begin
select c1, c2, c3
into var1, var2, var3
from some_table
where some_condition;
exception
when no_data_found yhen
var1 := null;
var2 := null;
var3 := null;
end;
At this point, you have what you want into var1 ... varn.
Assuming that the query may return several rows, you can do a BULK COLLECT into
arrays.
HTH,
SF
- --- Original Message --- -
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 06 Nov 2003 04:04:38
Hi All,
I'm a new member on this list. I'm not too familiar
with ORA, but I have some experience in other
RDBMS's.
I had run into the following problem.
My task is to write a query, which returns with an
empty (full of NULLs) row, if the condition is
false.
I'm thinking in outer joins.
I made a test. It returns all the matching rows (if
there are such rows), but unfortunately returns
with no row, if the condition is false.
Could someone help me, what could be the problem?
My oppinion is that it must be work fine... :-)
I use Oracle9i Enterprise Edition Release 9.2.0.1.0
.
My test 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) if the
string in the condition is 'a' or 'b', but returns
with no row (I'd like to see one row, with full of
NULLs) if I call it with 'c'... :-(
Could you tell me, why is this? Or any idea to
solve this task?
Thanks in advance:
Attila
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: New member / outer join problem
Hi Stephane, Review your logic. A row full of NULLs has no signification. If your statement is embedded into either some PL/SQL code or a 3rd generation language, Oracle will generate a 'no data found' error, and this is what you need to trap. I want to run it in SQL, not in PL/SQL. I Understand, what you mean - one of my collague asked me to solve this problem (the environment, in which he wants to embed this code requires some rows). Of course, the full NULL row has no meaning. But our DB is very-very dirty (no comment), so in normal case where I want to use this statement, 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 no rows if there is no matching row(s) in the right side. (If there are matching rows, the select will provide them). With this trick it will provide the required result... I don't know, if this is a bug, or this is the normal operation Thanks - Attila -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Outer join in SQL server - A very simple question
Sorry for the SQL Server post, working on SQL Server for the first time, and
this list is my only resource as of now. I'm reading the SQL Server join
syntax and trying it myself. Meanwhile, If i can get some expert help, it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly and t2 refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server which made my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause. This is not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or dissemination
of this message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper, untimely
or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join in SQL server - A very simple question
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 - A very simple question
Sorry for the SQL Server post, working on SQL Server for the
first time, and
this list is my only resource as of now. I'm reading the SQL
Server join
syntax and trying it myself. Meanwhile, If i can get some
expert help, it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly
and t2 refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server
which made my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause.
This is not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential
and may be privileged. Before opening attachments please
check them for viruses and defects. MindTree Consulting
Private Limited (MindTree) will not be responsible for any
viruses or defects or any forwarded attachments emanating
either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole or
in part is strictly prohibited. Please note that e-mails are
susceptible to change and MindTree shall not be liable for
any improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any unauthorized use or dissemination
of this message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper, untimely
or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join in SQL server - A very simple question
This does the same...
SELECT a1, b1, c1, d1
FROM
t1
left join t2 on t1.a=t2.b
left join t3 on t2.b=t3.c
left join t4 on t1.a2=t4.d
Ade
-Original Message-
Sent: 25 June 2003 12:55
To: Multiple recipients of list ORACLE-L
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 - A very simple question
Sorry for the SQL Server post, working on SQL Server for the
first time, and
this list is my only resource as of now. I'm reading the SQL
Server join
syntax and trying it myself. Meanwhile, If i can get some
expert help, it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly
and t2 refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server
which made my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause.
This is not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential
and may be privileged. Before opening attachments please
check them for viruses and defects. MindTree Consulting
Private Limited (MindTree) will not be responsible for any
viruses or defects or any forwarded attachments emanating
either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole or
in part is strictly prohibited. Please note that e-mails are
susceptible to change and MindTree shall not be liable for
any improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received this
message by mistake please notify the sender by return e-mail and delete
this message from your system. Any unauthorized use or dissemination of this
message in whole or in part is strictly prohibited. Please note that
e-mails are susceptible to change and MindTree shall not be liable for any
improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling
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 sure they
are still live.
[EMAIL PROTECTED] 06/25/03 02:49AM
Sorry for the SQL Server post, working on SQL Server for the first
time, and
this list is my only resource as of now. I'm reading the SQL Server
join
syntax and trying it myself. Meanwhile, If i can get some expert help,
it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly and t2
refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server which made
my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause. This is
not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return e-mail and
delete this message from your system. Any unauthorized use or
dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change and
MindTree shall not be liable for any improper, untimely or incomplete
transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join in SQL server - A very simple question
I think this does what you want:
select a1, b1, c1, d1
from t1 left join t2 on t1.a = t2.b
left join t3 on t2.b = t3.c
left join t4 on t1.a2 = t4.d ;
NB--I don't think this is necessarily a mssql question--oracle 9i (at least)
supports the same ANSI-92 standard join 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 Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Wednesday, June 25, 2003 4:55 AM
To: Multiple recipients of list ORACLE-L
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 - A very simple question
Sorry for the SQL Server post, working on SQL Server for the
first time, and
this list is my only resource as of now. I'm reading the SQL
Server join
syntax and trying it myself. Meanwhile, If i can get some
expert help, it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly
and t2 refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server
which made my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause.
This is not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential
and may be privileged. Before opening attachments please
check them for viruses and defects. MindTree Consulting
Private Limited (MindTree) will not be responsible for any
viruses or defects or any forwarded attachments emanating
either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole or
in part is strictly prohibited. Please note that e-mails are
susceptible to change and MindTree shall not be liable for
any improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received this
message by mistake please notify the sender by return e-mail and delete
this message from your system. Any unauthorized use or dissemination of this
message in whole or in part is strictly prohibited. Please note that
e-mails are susceptible to change and MindTree shall
RE: Outer join in SQL server - A very simple question
IIRC, the *= from Sybase and SQLServer is placed on the
opposite side of Oracle's (+) for the same affect.
i.e.
ORACLE:
select a.*,
b.*
from a,
b
where a.x(+) = b.x
Sybase/SQLServer:
select a.*,
b.*
from a,
b
where a.x =* b.x
The above 2 queries are equivalent; you can't just
replace the (+) with a *; you will also have to move
it to the other side of the equal sign.
-Original Message-
Sent: Wednesday, June 25, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L
This does the same...
SELECT a1, b1, c1, d1
FROM
t1
left join t2 on t1.a=t2.b
left join t3 on t2.b=t3.c
left join t4 on t1.a2=t4.d
Ade
-Original Message-
Sent: 25 June 2003 12:55
To: Multiple recipients of list ORACLE-L
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 - A very simple question
Sorry for the SQL Server post, working on SQL Server for the
first time, and
this list is my only resource as of now. I'm reading the SQL
Server join
syntax and trying it myself. Meanwhile, If i can get some
expert help, it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly
and t2 refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server
which made my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause.
This is not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential
and may be privileged. Before opening attachments please
check them for viruses and defects. MindTree Consulting
Private Limited (MindTree) will not be responsible for any
viruses or defects or any forwarded attachments emanating
either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return
e-mail and delete this message from your system. Any
unauthorized use or dissemination of this message in whole or
in part is strictly prohibited. Please note that e-mails are
susceptible to change and MindTree shall not be liable for
any improper, untimely or incomplete transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received this
message by mistake please notify the sender by return e-mail and delete
this message from your system. Any unauthorized use or dissemination of this
message in whole or in part is strictly prohibited. Please note that
e
RE: Outer join in SQL server - A very simple question
Thanx to all. As pointed by you guys, I learned the ANSI-92 standard join
syntax and it works perfectly with that.
And Darell thanx for the resources.
Regards
Naveen
-Original Message-
From: Darrell Landrum [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 25, 2003 7:44 PM
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
sure they
are still live.
[EMAIL PROTECTED] 06/25/03 02:49AM
Sorry for the SQL Server post, working on SQL Server for the first
time, and
this list is my only resource as of now. I'm reading the SQL Server
join
syntax and trying it myself. Meanwhile, If i can get some expert help,
it
will be more than useful
I'm simplifying the problem with the help of these test tables.
CREATE TABLE t1(A VARCHAR(10), A1 VARCHAR(10), A2 VARCHAR(10));
CREATE TABLE t2(B VARCHAR(10), B1 VARCHAR(10));
CREATE TABLE t3(C VARCHAR(10), C1 VARCHAR(10));
CREATE TABLE t4(D VARCHAR(10), D1 VARCHAR(10));
INSERT INTO t1 VALUES ('A', 'AA', '');
INSERT INTO t1 VALUES (null, '', '');
INSERT INTO t2 VALUES ('A', 'BB');
INSERT INTO t3 VALUES ('A', 'CC');
INSERT INTO t4 VALUES ('A', 'DD');
Table t1 refers to t2(through a) and t4(through a2) directly and t2
refers to
t3(through b).
I want to join t1 to t2, t3, t4 and retrieve all the rows inside t1
irrespective 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 C1 D1
-- -- -- --
AA BB CC DD
DD
I substituted the + with the * to use it in SQL Server which made
my
query look like this:
SELECT a1, b1, c1, d1
FROM t1, t2, t3, t4
WHERE t1.a =* t2.b
ANDt2.b =* t3.c
ANDt1.a2 = t4.d
But it fails with the error:
The table 't1' is an inner member of an outer-join clause. This is
not
allowed if the table also participates in a regular join clause.
How to overcome this? Or is the query wrong?
Regards
Naveen
DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for
viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received
this message by mistake please notify the sender by return e-mail and
delete this message from your system. Any unauthorized use or
dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change and
MindTree shall not be liable for any improper, untimely or incomplete
transmission.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged.
Before opening attachments please check them for viruses and defects. MindTree
Consulting Private Limited (MindTree) will not be responsible for any viruses or
defects or any forwarded attachments emanating either from within MindTree or outside.
If you have received this message by mistake please notify the sender
SQL statement 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 there are item numbers which do 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 incorrectly. Of course it isn't tuned either. If anyone is able to assist me, I would be very grateful. Thanks in advance, Saira SELECT F.ORDER_DATE, B.SHIP_NUM, C.SORT_NAME, F.ORDER_NUM, B.PPS_NUM, A.ITEM_NUM, D.DESC_1, A.TO_ALLOC_QTY, A.SHIPPED_QTY, A.BO_QTY, E.UOM FROM SHIP_L A, SHIP B, CUST_SHP C, ITEM D, UOM E, ORD F WHERE A.SHIP_ID=B.SHIP_ID AND B.CUST_NUM=C.CUST_NUM AND B.SHIP_NUM=C.SHIP_NUM AND A.ITEM_NUM=D.ITEM_NUM AND D.STOCK_UOM_ID=E.UOM_ID AND B.ORD_ID=F.ORD_ID AND A.TO_ALLOC_QTY A.SHIPPED_QTY AND F.DIV_CODE='01' AND F.CANCELLED='N' AND A.WHSE_CODE='HL1' AND B.PPS_PRINTED='Y' AND F.DIV_CODE='01' AND F.CANCELLED='N' AND B.CUST_NUM='2' AND F.ORDER_DATE=SYSDATE-1 ORDER BY F.ORDER_DATE, C.SORT_NAME; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL statement problem - outer join where?
FROM ., ITEM_C C WHERE . AND A.ITEM_NUM = C.ITEM_NUM(+) AND B.CUST_NUM = C.CUST_NUM(+) Regards Naveen -Original Message- From: Saira Somani [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 8:55 PM To: Multiple recipients of list ORACLE-L Subject: SQL statement 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 there are item numbers which do 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 incorrectly. Of course it isn't tuned either. If anyone is able to assist me, I would be very grateful. Thanks in advance, Saira SELECT F.ORDER_DATE, B.SHIP_NUM, C.SORT_NAME, F.ORDER_NUM, B.PPS_NUM, A.ITEM_NUM, D.DESC_1, A.TO_ALLOC_QTY, A.SHIPPED_QTY, A.BO_QTY, E.UOM FROM SHIP_L A, SHIP B, CUST_SHP C, ITEM D, UOM E, ORD F WHERE A.SHIP_ID=B.SHIP_ID AND B.CUST_NUM=C.CUST_NUM AND B.SHIP_NUM=C.SHIP_NUM AND A.ITEM_NUM=D.ITEM_NUM AND D.STOCK_UOM_ID=E.UOM_ID AND B.ORD_ID=F.ORD_ID AND A.TO_ALLOC_QTY A.SHIPPED_QTY AND F.DIV_CODE='01' AND F.CANCELLED='N' AND A.WHSE_CODE='HL1' AND B.PPS_PRINTED='Y' AND F.DIV_CODE='01' AND F.CANCELLED='N' AND B.CUST_NUM='2' AND F.ORDER_DATE=SYSDATE-1 ORDER BY F.ORDER_DATE, C.SORT_NAME; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Try Again: Outer Join with SUBSTR
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), rtrim(lisa.unittype), rtrim(lisa.macaddress), rtrim(lisa.ipaddress), rtrim(lisa.operatingsystem), rtrim(lisa.servername), rtrim(lisa.lastrundate), rtrim(lisa.serialnumber), rtrim(lisa.person), rtrim(lisa.location), rtrim(smarts.pcmacaddress), rtrim(smarts.pcip), rtrim(smarts.portname), rtrim(smarts.portdesc), rtrim(smarts.switchname), rtrim(smarts.switchipaddres! s), rtrim(smarts.switchlocation), rtrim(smarts.vendor), rtrim(smarts.site), rtrim(smarts.custname), rtrim(smarts.scandate), rtrim(smarts.giltbis)FROM kompneti, komp, sysstand, person, c_lisa_data lisa, kompsys, syspers, c_smarts_data smarts WHERE kompneti.mac_adr = lisa.macaddress and kompneti.giltbis = '2100-01-01-00.00.00.00' and kompneti.ident = komp.ident and komp.ident = kompsys.ident and kompsys.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = sysstand.system and sysstand.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.00' and syspers.pers_nr = person.pers_nr(+) and smarts.giltbis = '2100-01-01-00.00.00.00' and! lisa.macaddress = substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2) (+) /Or Use:substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2)(+)=lisa.macaddress/Or Use:(substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2))(+)=lisa.macaddress! / Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
RE: Try Again: Outer Join with SUBSTR
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 Message- From: WLSH [mailto:[EMAIL PROTECTED]] 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), rtrim(lisa.unittype), rtrim(lisa.macaddress), rtrim(lisa.ipaddress), rtrim(lisa.operatingsystem), rtrim(lisa.servername), rtrim(lisa.lastrundate), rtrim(lisa.serialnumber), rtrim(lisa.person), rtrim(lisa.location), rtrim(smarts.pcmacaddress), rtrim(smarts.pcip), rtrim(smarts.portname), rtrim(smarts.portdesc), rtrim(smarts.switchname), rtrim(smarts.switchipaddres! s), rtrim(smarts.switchlocation), rtrim(smarts.vendor), rtrim(smarts.site), rtrim(smarts.custname), rtrim(smarts.scandate), rtrim(smarts.giltbis) FROM kompneti, komp, sysstand, person, c_lisa_data lisa, kompsys, syspers, c_smarts_data smarts WHERE kompneti.mac_adr = lisa.macaddress and kompneti.giltbis = '2100-01-01-00.00.00.00' and kompneti.ident = komp.ident and komp.ident = kompsys.ident and kompsys.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = sysstand.system and sysstand.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.00' and syspers.pers_nr = person.pers_nr(+) and smarts.giltbis = '2100-01-01-00.00.00.00' and ! lisa.macaddress = substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2) (+) / Or Use: substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2)(+)=lisa.macaddress / Or Use: (substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2))(+)=lisa.macaddress! /
Re: Try Again: Outer Join with SUBSTR
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) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2))(+)=lisa.macaddress makes smarts the deficient table, but smarts.giltbis = '2100-01-01-00.00.00.00' and eliminates the outer-ness of the join, and loses any appended rows. similarly with: kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.00' and the join to syspers ceases to be outer because of the literal comparison. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 20 March 2003 14:44 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(smarts.giltbis) FROM kompneti, komp, sysstand, person, c_lisa_data lisa, kompsys, syspers, c_smarts_data smarts WHERE kompneti.mac_adr = lisa.macaddress and kompneti.giltbis = '2100-01-01-00.00.00.00' and kompneti.ident = komp.ident and komp.ident = kompsys.ident and kompsys.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = sysstand.system and sysstand.giltbis = '2100-01-01-00.00.00.00' and kompsys.system = syspers.system(+) and syspers.giltbis = '2100-01-01-00.00.00.00' and syspers.pers_nr = person.pers_nr(+) and smarts.giltbis = '2100-01-01-00.00.00.00' and lisa.macaddress = substr(pcmacaddress,10,2) || substr(smarts.pcmacaddress,13,2) || substr(smarts.pcmacaddress,16,2) || substr(smarts.pcmacaddress,19,2) || substr(smarts.pcmacaddress,22,2) || substr(smarts.pcmacaddress,25,2) (+) / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Problem with Outer Join
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 Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. 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-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with Outer Join
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 records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L 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 Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. 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-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with Outer Join
Tom, I think by the post he wants to limit result set by employee_id for ex. employee_id=123 Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: Problem with Outer Join Sent by: [EMAIL PROTECTED] 08/22/2002 08:58 AM Please respond to ORACLE-L 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 records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L 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 Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. 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-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author
RE: Problem with Outer Join
Rick, That's not how I read it. He wants all dates returned showing whether an employee was present or not. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Tom, I think by the post he wants to limit result set by employee_id for ex. employee_id=123 Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: Problem with Outer Join Sent by: [EMAIL PROTECTED] 08/22/2002 08:58 AM Please respond to ORACLE-L 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 records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L 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 Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. 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-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note
outer join
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 the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: outer join
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-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 the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: outer join
Can you post the SQL? What version of the DB are you running it against? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, March 14, 2002 3:33 AM To: Multiple recipients of list 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 the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
outer join
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 official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: outer join
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 - but the question does not pertain explicitely fo views, but to the sql and the tables it is accessing. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 12, 2002 8:23 AM To: Multiple recipients of list ORACLE-L 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 official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: outer join
Hi, Perhaps this note may help. Rgds Rick 1 INTRODUCTION The purpose of this technical note is to provide some guidelines on how to use the outer join facility provided by ORACLE. 1.1 Outer Join Semantics - Definitions The following terms, used to describe the operation on outer joins, are defined : - 'outer-join column' - a column reference followed by the symbol (+), e.g. EMPNO(+) and DEPT.DEPTNO(+) are outer join columns 'simple predicate' - a logical expression containing no AND's, OR's, or NOT's ( usually a simple relation such as A = B ) 'outer join predicate' - a simple predicate containing one or more outer join columns 2 OUTER JOIN SYNTAX - RULES An outer join predicate may contain outer join columns from two or more
Re: outer join
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 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 official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: outer join
You can have multiple outer joins in the where clause but one table can be outer-joined to one other table only. For example you can write: select tb1.col_1 from table1 tb1, table2 tb2, table3 tb3 where tb1.some_col = some value and tb1.col_a(+) = tb2.col_a and tb2.col_b(+) = tb3.col_b 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 has changed in 8i, or 9i Witold On 12 Mar 2002 at 5:23, [EMAIL PROTECTED] wrote: 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 official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: outer join
That is whats happening, e.g. i have (cut down), create view all_data as(select swname ,business ,org,waf from licence l,business b, organisation o,wafs w where l.id = o.id (+) and l.wafid = w.id (+) etc. How can i get around this ? Igor Neyman ineyman To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @perceptron.ccc: om Subject: Re: outer join Sent by: root 12/03/2002 14:18 Please respond to ORACLE-L 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 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 official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Outer join sql help?
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, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql help?
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 to see what records seem to be missing.
Look especially closely at the date columns. Your BETWEEN clause only
selects dates with time stamps between 1/1/2001 00:00:00 and 12/31/2001
00:00:00 - note that records with dates of 12/31 will not be selected if
they have a time stamp 0. You might try changing the between clause to:
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001 235959','MM/DD/ hh24miss')
Hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 12, 2001 12:42 PM
To: Multiple recipients of list ORACLE-L
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, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND
l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql help?
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 TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
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 l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql help?
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,
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 TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
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 l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql SOLVED!
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 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 TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Need Good OUTER Join
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 may or may not have any records. Below is my current query and table layout. Thanks for any help you can give. Sincerely, Woody === 1 select p.page_id, substr(p.page_title,1,30), p.page_parent_id, x.security_id 2 from pages p, users_x_pages x, users u 3 where p.page_id = x.page_id(+) 4 and x.user_id = u.user_id(+) 5 and u.user_id(+) = 5 6* order by page_title SQL / PAGE_ID SUBSTR(P.PAGE_TITLE,1,30) PAGE_PARENT_ID SECURITY_ID -- -- -- --- 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 1 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 2 53 Communications SQL 1 47 DMS SQL 2 47 DMS SQL 2 63 DMS Hot Topics SQL 2 58 Division of Retirement SQL 2 62 Governor's Area SQL SQL 56 HR Management SQL 2 49 Inspector General SQL SQL 51 Legislative Affairs SQL SQL 50 Personnel Management SQL 2 52 Planning Budget SQL SQL 61 Secretary Bio SQL SQL 55 State Fleet Management SQL SQL 60 State Group Insurance SQL SQL 57 State Purchasing SQL SQL 59 Supplier Diversity SQL SQL 20 rows selected. ole0.bmp Description: Windows bitmap
RE: Need Good OUTER Join
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 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 may or may not have any records. Below is my current query and table layout. === 1 select p.page_id, substr(p.page_title,1,30), p.page_parent_id, x.security_id 2 from pages p, users_x_pages x, users u 3 where p.page_id = x.page_id(+) 4 and x.user_id = u.user_id(+) 5 and u.user_id(+) = 5 6* order by page_title SQL / PAGE_ID SUBSTR(P.PAGE_TITLE,1,30) PAGE_PARENT_ID SECURITY_ID -- -- -- --- 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 1 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 2 53 Communications SQL 1 47 DMS SQL 2 47 DMS SQL 2 63 DMS Hot Topics SQL 2 58 Division of Retirement SQL 2 62 Governor's Area SQL SQL 56 HR Management SQL 2 49 Inspector General SQL SQL 51 Legislative Affairs SQL SQL 50 Personnel Management SQL 2 52 Planning Budget SQL SQL 61 Secretary Bio SQL SQL 55 State Fleet Management SQL SQL 60 State Group Insurance SQL SQL 57 State Purchasing SQL SQL 59 Supplier Diversity SQL SQL 20 rows selected.
RE: Need Good OUTER Join
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. USERS_X_PAGES may or may not have any records. Below is my current query and table layout. Thanks for any help you can give. Sincerely, Woody === 1 select p.page_id, substr(p.page_title,1,30), p.page_parent_id, x.security_id 2 from pages p, users_x_pages x, users u 3 where p.page_id = x.page_id(+) 4 and x.user_id = u.user_id(+) 5 and u.user_id(+) = 5 6* order by page_title SQL / PAGE_ID SUBSTR(P.PAGE_TITLE,1,30) PAGE_PARENT_ID SECURITY_ID -- -- -- --- 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 1 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 2 53 Communications SQL 1 47 DMS SQL 2 47 DMS SQL 2 63 DMS Hot Topics SQL 2 58 Division of Retirement SQL 2 62 Governor's Area SQL SQL 56 HR Management SQL 2 49 Inspector General SQL SQL 51 Legislative Affairs SQL SQL 50 Personnel Management SQL 2 52 Planning Budget SQL SQL 61 Secretary Bio SQL SQL 55 State Fleet Management SQL SQL 60 State Group Insurance SQL SQL 57 State Purchasing SQL SQL 59 Supplier Diversity SQL SQL 20 rows selected. [Woody Mckay] There are three tables involved: USERS - user_id PK USERS_X_PAGES - user_id - fk to users - page_id - fk to pages - security_id PAGES - page_id pk - page_title
RE: Need Good OUTER Join
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 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 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 may or may not have any records. Below is my current query and table layout. === 1 select p.page_id, substr(p.page_title,1,30), p.page_parent_id, x.security_id 2 from pages p, users_x_pages x, users u 3 where p.page_id = x.page_id(+) 4 and x.user_id = u.user_id(+) 5 and u.user_id(+) = 5 6* order by page_title SQL / PAGE_ID SUBSTR(P.PAGE_TITLE,1,30) PAGE_PARENT_ID SECURITY_ID -- -- -- --- 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 1 48 Building Construction Manage SQL 2 48 Building Construction Manage SQL 2 53 Communications SQL 1 47 DMS SQL 2 47 DMS SQL 2 63 DMS Hot Topics SQL 2 58 Division of Retirement SQL 2 62 Governor's Area SQL SQL 56 HR Management SQL 2 49 Inspector General SQL SQL 51 Legislative Affairs SQL SQL 50 Personnel Management SQL 2 52 Planning Budget SQL SQL 61 Secretary Bio SQL SQL 55 State Fleet Management SQL SQL 60 State Group Insurance SQL SQL 57 State Purchasing SQL SQL 59 Supplier Diversity SQL SQL 20 rows selected.
outer join problem
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 = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: outer join problem
May be???: select xyz from t1 a, t1 b, t1 c, t2 d, t3 e, t4 f where d.id_prop = a.id_pi_template(+) and e.id_prop = b.id_pi_template(+) and f.id_prop = c.id_pi_template(+) and a.rowid = b.rowid and a.row_id = c.rowid and ... Jan Pruner Dne ?t 6. zá?í 2001 19:26 jste napsal(a): 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 = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: outer join problem
Why are you trying to join three different tables to values which may not exists? Are those three tables related to each other outside of what you show here? If not, and this statement worked, you would end up with a matrix join between those three tables. Is that what you are looking 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(+) and t_discount.id_prop = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: outer join problem
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, but hard-joins the other tables together. Would this work? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 06, 2001 1:27 PM To: Multiple recipients of list ORACLE-L 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 = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: outer join problem
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 recipients of list ORACLE-L Subject: outer join problem 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 = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
urgent..! ANSI Standard SQL for Outer Join (ORACLE SQL SERVER)
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 the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rangachari Sundar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: urgent..! ANSI Standard SQL for Outer Join (ORACLE SQL SERVER)
Why don't you try it? Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 8:35 AM 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 the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rangachari Sundar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: urgent..! ANSI Standard SQL for Outer Join (ORACLE SQL SERVER)
Hello Rangachari, Oracle9i supports the ANSI join syntax. I just finished an article on it that should appear in the Nov/Dec (I think) issue of Oracle Magazine. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://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 join dept on emp.deptno = dept.deptno (Something like this) RS Bye RS Sundar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
