RE: Multiple Outer Joins

[Simoneau, Roger]

Never mind outlining the relationship, a quick 5 minute crash course in Ansi SQL gives me an interpreter....

Try the following on your dataset:

SELECT *
FROM   tblemployeeinfo a,
       tblunrepaired_adv b,
       tbldatabystate c
WHERE  b.emailed = 0
AND    b.username = a.username(+)
AND    b.policy_state = c.statecode(+)

Roger S.

PLEASE NOTE:
This communication, including any attached documentation,
is intended only for the person or entity to which it is addressed,
and may contain confidential, personal and/or privileged information.
Any unauthorized disclosure, copying, or taking action on the contents
is strictly prohibited. If you have received this message in error,
please contact us immediately so we may correct our records.
Please then delete or destroy the original transmission and any subsequent reply.
Thank you.

[Jeff Tibb]

Correct, if we accepted your first set of assumptions.

SELECT *
FROM   tblemployeeinfo a,
       tblunrepaired_adv b,
       tbldatabystate c
WHERE  b.emailed = 0
AND    b.username = a.username(+)
AND    b.policy_state(+) = c.statecode

Just move that last (+) sign to the other table.

That is what I am looking for, however that request is illegal in
Oracle syntax.

Jeff


--- In toad@..., "Simoneau, Roger" <rsimonea@...> wrote:
>
> Never mind outlining the relationship, a quick 5 minute crash
course in Ansi SQL gives me an interpreter....

>
> Try the following on your dataset:
>
> SELECT *
> FROM   tblemployeeinfo a,
>        tblunrepaired_adv b,
>        tbldatabystate c
> WHERE  b.emailed = 0
> AND    b.username = a.username(+)
> AND    b.policy_state = c.statecode(+)
>
> Roger S.
>
> PLEASE NOTE:
> This communication, including any attached documentation,
> is intended only for the person or entity to which it is addressed,
> and may contain confidential, personal and/or privileged

information.
> Any unauthorized disclosure, copying, or taking action on the
contents
> is strictly prohibited. If you have received this message in error,
> please contact us immediately so we may correct our records.
> Please then delete or destroy the original transmission and any
subsequent reply.
> Thank you.
>

[Simoneau, Roger]

Actually, I built three tables with a multitude of data in them (not complete as far as full testing goes), ran your query over them using the Ansi syntax (I'm on 10g), then re-built the query with the "interpretation" and had the same result set returned.
 
I understand what you're looking for (or believe you are) but if you run the query I sent and compare the result set returned with your original, I think it'll be pretty close if not dead on.
 
Roger S.
 

-----Original Message-----
From: toad@... [mailto:toad@...]On Behalf Of Jeff Tibb
Sent: Thursday, July 17, 2008 3:19 PM
To: toad@...
Subject: [toad] Re: Multiple Outer Joins



Correct, if we accepted your first set of assumptions.

SELECT *
FROM tblemployeeinfo a,
tblunrepaired_adv b,
tbldatabystate c
WHERE b.emailed = 0
AND b.username = a.username(+)
AND b.policy_state(+) = c.statecode

Just move that last (+) sign to the other table.

That is what I am looking for, however that request is illegal in
Oracle syntax.

Jeff

--- In toad@yahoogroups. <mailto:toad%40yahoogroups.com> com, "Simoneau, Roger" <rsimonea@...> wrote:
>
> Never mind outlining the relationship, a quick 5 minute crash
course in Ansi SQL gives me an interpreter....

>
> Try the following on your dataset:
>
> SELECT *
> FROM tblemployeeinfo a,
> tblunrepaired_adv b,
> tbldatabystate c
> WHERE b.emailed = 0
> AND b.username = a.username(+)
> AND b.policy_state = c.statecode(+)
>
> Roger S.
>
> PLEASE NOTE:
> This communication, including any attached documentation,
> is intended only for the person or entity to which it is addressed,
> and may contain confidential, personal and/or privileged

information.
> Any unauthorized disclosure, copying, or taking action on the
contents
> is strictly prohibited. If you have received this message in error,
> please contact us immediately so we may correct our records.
> Please then delete or destroy the original transmission and any
subsequent reply.
> Thank you.
>



 


PLEASE NOTE:
This communication, including any attached documentation,
is intended only for the person or entity to which it is addressed,
and may contain confidential, personal and/or privileged information.
Any unauthorized disclosure, copying, or taking action on the contents
is strictly prohibited. If you have received this message in error,
please contact us immediately so we may correct our records.
Please then delete or destroy the original transmission and any subsequent reply.
Thank you.

[SCHROEDER, NATHAN E [AG/1000]]

I think Roger's right, Jeff: you had the Oracle Outer Join syntax
reversed (the pluses on the wrong side).  The query he gave you below is
the proper translation of your original ANSI query, and will be accepted
by Oracle (and run fairly efficiently, if the correct columns are
indexed).

Nate Schroeder
IT Commercial Technical Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
314-694-2592

-----Original Message-----
From: toad@... [mailto:toad@...] On Behalf Of
Simoneau, Roger
Sent: Thursday, July 17, 2008 4:15 PM
To: toad@...
Subject: RE: [toad] Multiple Outer Joins

Never mind outlining the relationship, a quick 5 minute crash course in
Ansi SQL gives me an interpreter....

Try the following on your dataset:

SELECT *
FROM   tblemployeeinfo a,
       tblunrepaired_adv b,
       tbldatabystate c
WHERE  b.emailed = 0
AND    b.username = a.username(+)
AND    b.policy_state = c.statecode(+)

Roger S.


---------------------------------------------------------------------------------------------------------
This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.


All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware". Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying this e-mail or any attachment.
---------------------------------------------------------------------------------------------------------