outer join oracle query translate in sql server

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||Hi,
thank you very much..it worked excellent

Originally posted by Brett Kaiser
I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||if your oracle install is 9i
the ansi join syntax actually works
after 20 years!!!!!

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>

Outer Join Challenge

I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>sql

OUTER JOIN

Oi! What follows is a hypothetical situation, but it is a totally analogous to a real problem Im having, but provides an easier model to understand.

Imagine that you have database-driven battleship game and its time to render the board. Also imagine that you have to render more than one board and that the ships are all the size of one point on the grid.

One sql result you need is a list of all the points on the grid, regardless of whether or not there is a ship on it. This will make rendering much easier for you, because you can simply look at the record index to determine if a ship is present. The data that is stored about the position of the ships consists of one record containing the grid index and ship name.

One possible way to retrieve this data is to build a table that you will not change which contains a record for each point on the grid. Is it possible to union or join on this table to retrieve a list of results that contain both unoccupied locations and occupied ones?

Here is what I've come up with, but it contains results that have a null location when there are no ship records:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
WHERE
Grids.PlayerID = 1
UNION-- (**not** UNION ALL)
SELECT
Grids.GridID,
GridLocations.GridLocation,
(SELECT ShipName FROM Ships WHERE GridID = Grids.GridID AND GridLocation = GridLocations.GridLocation)
FROM
GridLocations, Grids
WHERE
Grids.PlayerID = 1

In the first sql statement, why not just do a left join? That will select everything from the grid, and then the ship info if it is there.|||Something about Ships to Grids and GridLocations to Ships is preventing this from happening as you would expect:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
LEFT JOIN GridLocations
ON GridLocations.GridLocation = Ships.GridLocation
WHERE
Grids.PlayerID = 1

this would return a list of ships if there are any, and if not, a list of grids!
(In this silly scenario, there can be multiple grids with PlayerID = 1|||I meant something like this:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
LEFT JOIN Ships
ON Grids.GridID = Ships.GridID
WHERE
Grids.PlayerID = 1

It's hard to tell since I don't have any data to work with to simulate. :)|||Hmm

This would result in only a list of grids, or a list of ships and grids. Like if there were 4 ships scattered in 2 grids:

GridID GridLocation Name
-----------
1 1 MyShip1
1 24 MyShip2
2 18 MyShip3
2 20 MyShip4

However, what I want is a list of all grid locations on all grids:

GridID GridLocation Name
-----------
1 1 MyShip1
1 2 NULL
1 3 NULL
1 4 NULL
1 5 NULL
1 6 NULL
1 7 NULL
...
2 1 NULL
2 2 NULL
2 3 NULL
2 4 NULL
2 5 NULL
2 6 NULL
2 7 NULL|||You need to post some DDL with some sample data, otherwise my guessing won't help.