TSQL LEFT OUTER JOIN to PL/SQL Join Help

[jbodine]

Ok. I am in the painstaking process of converting a bunch of reports with stored procs written in TSQL (MSSqlserver)
to PL/SQL.

I am having issues with one of my joins and getting it to work right in PL/SQL
IN TSQL its looks something like this:

SELECT
*
FROM ParticipantSegment PS

LEFT OUTER JOIN ParticipantSegment AS ToPS ON
ToPS.FromSegmentId = PS.ParticipantSegmentId
AND ToPS.EntityTypeId = 1
AND (TOPS.RouteReasonId NOT IN (14,12) OR TOPS.RouteReasonID IS NOT NULL)

Keep in mind that the AND conditions only apply to the JOIN, not the entire query, which is the problem I am having in Oracle *817 btw*. Please keep in mind that I do have other things in my where clause and the below condidtions are ones that I want to apply ONLY as they relate to the join condition.

I have tried:
SELECT
*
FROM
PARTICIPANTSEGMENT PS
,PARTICIPANTSEGMENT ToPS
WHERE

AND ToPS.FromSegmentID (+) = PS.ParticipantSegmentId
AND ToPS.EntityTypeId = 1
AND ToPS.RouteReasonId NOT IN (14,12)
AND ToPS.RouteReasonId IS NOT NULL



SELECT
*
FROM
PARTICIPANTSEGMENT PS
,PARTICIPANTSEGMENT ToPS
WHERE

AND (ToPS.FromSegmentID (+) = PS.ParticipantSegmentId
AND ToPS.EntityTypeId = 1
AND ToPS.RouteReasonId NOT IN (14,12)
AND ToPS.RouteReasonId IS NOT NULL)



SELECT
*
FROM
PARTICIPANTSEGMENT PS
,PARTICIPANTSEGMENT ToPS
WHERE
AND (ToPS.FromSegmentID (+) = PS.ParticipantSegmentId
AND ToPS.EntityTypeId = 1)
AND (ToPS.RouteReasonId NOT IN (14,12) AND ToPS.RouteReasonId IS NOT NULL)

 

[LKBrwnDBA]

Then try this:

SELECT
*
FROM
PARTICIPANTSEGMENT PS
,PARTICIPANTSEGMENT ToPS
WHERE ToPS.FromSegmentID (+) = PS.ParticipantSegmentId
AND ToPS.EntityTypeId(+) = 1
AND (ToPS.RouteReasonId(+) IS NOT NULL
AND ToPS.RouteReasonId(+) NOT IN (14,12));

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

It looks like your problem might be a superfluous AND -

WHERE
AND ToPS.FromSegmentID (+) = PS.ParticipantSegmentId

should probably be

WHERE
ToPS.FromSegmentID (+) = PS.ParticipantSegmentId

The notion of
(ToPS.RouteReasonId NOT IN (14,12)OR ToPS.RouteReasonId IS NOT NULL) strikes me as a little odd - If RouteReasonID has a value of 12, this will still evaluate to TRUE since 12 IS NOT NULL! I suspect you really wanted an AND instead of an OR.

Try this and see if it works for you:

SELECT
*
FROM
PARTICIPANTSEGMENT PS
,PARTICIPANTSEGMENT ToPS
WHERE
AND ToPS.FromSegmentID (+) = PS.ParticipantSegmentId
AND ToPS.EntityTypeId(+) = 1
AND ToPS.RouteReasonId(+) NOT IN (14,12)
AND ToPS.RouteReasonId(+) IS NOT NULL;

 

[jbodine]

Thanks all. The WHERE AND ToPS was a typo on my part, I have a huge stored procs, so i was cutting out just the parts that I needed help with so you did not have to strain to understand it all.

In any cause the (+)= on the other conditions did the trick. I am new to Oracle, and have been tasked with adding Oracle support to our product and im the TSQL guy....they layed off the Oracle guy two weeks ago....

Thanks!

Jim