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))