Outer Join Not Working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cindy - 13 Oct 2006 18:08 GMT

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?

Reply to this Message

William - 13 Oct 2006 21:11 GMT

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.

> I'm working with SQL Reporting Services 2005. I'm trying to list
> opportunities that do not have a future open activity.
[quoted text clipped - 14 lines]
>
> What am I doing wrong?

Reply to this Message

Cindy - 13 Oct 2006 22:56 GMT

In my case the data tab is NOT working.

Reply to this Message

susiedba@hotmail.com - 13 Oct 2006 23:09 GMT

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

> In my case the data tab is NOT working.

Reply to this Message

Charles Wang[MSFT] - 16 Oct 2006 06:49 GMT

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.
======================================================

Reply to this Message

Cindy - 30 Nov 2006 18:14 GMT

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

Reply to this Message