Outer Join Not Working

I'm working with SQL Reporting Services 2005. I'm trying to list
opportunities that do not have a future open activity.
Below is my SQL statement. StateCodes of 0 are open opportunities and open
activities
SELECT Opportunity.name
FROM Activity RIGHT OUTER JOIN Opportunity
ON Activity.RegardingObjectId = Opportunity.opportunityid
WHERE (Activity.StateCode = 0) AND
(Activity.ScheduledStart > { fn NOW() }) AND
(Opportunity.statecode = 0)
It is only returning matched records when I run the query in the Data tab.
There are no instances of an Opportunity without an activity (and we have
loads!)
What am I doing wrong?It has been my experience that when the data tab works and the report
doesn't, and no errors are present, the setting of parameters is usually the
culprit. If you set default parameters, make sure that "O" and 0 (zero) are
not confused.
"Cindy" wrote:
> I'm working with SQL Reporting Services 2005. I'm trying to list
> opportunities that do not have a future open activity.
> Below is my SQL statement. StateCodes of 0 are open opportunities and open
> activities
> SELECT Opportunity.name
> FROM Activity RIGHT OUTER JOIN Opportunity
> ON Activity.RegardingObjectId = Opportunity.opportunityid
> WHERE (Activity.StateCode = 0) AND
> (Activity.ScheduledStart > { fn NOW() }) AND
> (Opportunity.statecode = 0)
> It is only returning matched records when I run the query in the Data tab.
> There are no instances of an Opportunity without an activity (and we have
> loads!)
> What am I doing wrong?
>
>|||In my case the data tab is NOT working.|||SSRS is just flaky as ever.
I would reccomend calling Microsoft once an hour and demanding to know
when SP2 is coming out since this product is basically just not
usable-- since it's soooooo buggy
Sorry; I just dont want to candy-coat it any longer
-Aaron
Cindy wrote:
> In my case the data tab is NOT working.|||Hi,
My understanding of your issue is:
Assume that your opportunity table has the following records:
OpportunityName OpportunityID StateCode
O1 1 0
O2 2 1
O3 3 0
O4 4 0
your activity table has the following records
Owner RegardingObjectId StateCode
W1 1 1
W2 2 0
W3 3 0
W4 4 0
You wanted to get the result:
OpportinityName Owner OpportunityID StateCode
O1 NULL 1 0
O3 W3 3 0
O4 W4 4 0
If I have misunderstood, please let me know.
Please try the following statement:
SELECT Opportunity.name
FROM Activity RIGHT OUTER JOIN Opportunity
ON ( Activity.RegardingObjectId = Opportunity.opportunityid AND
Activity.StateCode = 0 AND Activity.ScheduledStart > { fn NOW() })
WHERE Opportunity.statecode = 0
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I finally got this working with some help from the CRM Developer
newsgroup -- but the answer was all about SQL syntax. See below:
select opportunityid, name from FilteredOpportunity
where opportunityid not in
(select regardingobjectid from filteredactivitypointer where
(regardingobjecttypecode = 3) and (statecode = 0))

Outer Join Help!

The first query below returns 46 rows (correct result set) while the second
one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullPlease do not post the same question independently to multiple newsgroups.
Your original post has already been answered in .server.
"Arul" <Arul@.discussions.microsoft.com> wrote in message
news:68D344E0-2384-4C56-95EE-EDF80DB5869A@.microsoft.com...
> The first query below returns 46 rows (correct result set) while the
second
> one
> returns all of the records from app_type table. The second query is
similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||Arul,
The second query is not the equivalent of the first. It should be:
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Arul" wrote:

> The first query below returns 46 rows (correct result set) while the secon
d
> one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
>|||>> Can someone help me figure out whats wrong with the second query? <<
It is not SQL! There is no such operator as *=. And when it was a
dialect, it never worked right anyway. Why are you using it or are you
trtying migrate old code?

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

OSQL Ouput Formatting.

Hi,
When I execute a query in OSQL using Query Analyser the output is not well
formatted.
Sample Query is given below.
Can someone suggest me a way to get good readable format in QA.
Thanks in advance.
-Kumar.
--***--
SELECT TOP 3 * FROM pubs..authors
--Using Command Prompt:
--osql -E -Q "SELECT TOP 3 * FROM authors" -d pubs
--Using Query Analyser:
DECLARE @.vcSQLCmd VARCHAR(1000)
SET @.vcSQLCmd = 'osql -E -Q "SELECT * FROM authors" -d pubs'
EXEC master..xp_cmdShell @.vcSQLCmd
--***--
--SeequellWhy are you using osql, if you want the results in QA? Why not use it
directly?
Anith|||Let me give more details...
I have few script files and those need to be run against few Db Servers/DBs.
I would like to run these scripts from my computer by just changing
Server/Db name.
--Seequell
"Anith Sen" wrote:

> Why are you using osql, if you want the results in QA? Why not use it
> directly?
> --
> Anith
>
>|||>> I have few script files and those need to be run against few Db
OK, with osql there results are formatted to display at the command prompt.
And there is not much you can do to change it in Query Analyzer.
In you case, if QA formatting is important consider using 4 part naming (
server.database.owner.object ) or a pass-though query ( like OPENQUERY,
OPENROWSET etc. ) to access data from external servers. Details about linked
servers ( 4 part naming ) as well as using distributed queries are well
documented in SQL Server Books Online.
Anith|||Thanks a lot Anith.
--Seequell
"Anith Sen" wrote:

> OK, with osql there results are formatted to display at the command prompt
.
> And there is not much you can do to change it in Query Analyzer.
> In you case, if QA formatting is important consider using 4 part naming (
> server.database.owner.object ) or a pass-though query ( like OPENQUERY,
> OPENROWSET etc. ) to access data from external servers. Details about link
ed
> servers ( 4 part naming ) as well as using distributed queries are well
> documented in SQL Server Books Online.
> --
> Anith
>
>|||Hi Anith,
I found a -w switch in OSQL for changing the width of the output, I think.
I am exploring more on it. Thanks.
--Seequell
"Seequell" wrote:
> Thanks a lot Anith.
> --Seequell
>
> "Anith Sen" wrote:
>