Query working in SSMS but not in SSRS?

Question

I am using SQL Server 2012 Visual Studio 2010.

I've written the following query

SELECT  g.membercode, l.Value as AccountNum, ll.Value as Custodian, MAX(r.positiondate) as PositionDate
  FROM [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] g
  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPositionRecon] r
  ON g.MemberID = r.PortfolioID
  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseCustomLabels] l
  on g.MemberID = l.PortfolioBaseID
    LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseCustomLabels] ll
  on g.MemberID = ll.PortfolioBaseID
  Where g.portfoliogroupcode = 'open'
  and PositionDate is NULL
  and l.label = '$numname'
  and ll.label = '$custdn'
  and ll.Value not in ('mbt', 'whittier')
  group by r.portfoliocode, g.membercode, l.Value, ll.Value
  order by ll.Value, g.MemberCode

In SQL Server Management Studio I get the following desired result set

membercode  AccountNum  Custodian   PositionDate
account1    123456      mssb       NULL
account2    78910       mssb       NULL
account3    11121314    mssb       NULL
account4    151617018   mssb       NULL
account5    19202122    mssb       NULL

However using the identical query in SSRS I am getting the following undesired result set where account name is showing up in the Custodian column and Member Code is 0

Member Code Account Number  Custodian   Position Date   Row Count
0           123456          account1                        1   
0           78910           account2                        2   
0           11121314        account3                        3   
0           151617018       account4                        4   
0           19202122        account5                        5

Any idea why this query works in SSMS but not SSRS?

Well - seeing as you select more columns and other aliases in the second query, they're not really identical, and it would be possible there might be an error in one or the other you've not spotted? — Allan S. Hansen
– Allan S. Hansen, Commented Nov 21, 2013 at 19:26
I'm not sure I understand what the group by r.portfoliocode is accomplishing. Or why you are obtaining MAX(r.positiondate) when you have constrained this to NULL in the WHERE clause. I would try a simplified query. Otherwise, I second Hansen. The SSRS result set does not look like it came from the same query. — criticalfix
– criticalfix, Commented Nov 21, 2013 at 19:46
The point of the query is to cross reference two tables where a value from table g is not present in table r. In addition I want to know when the last date information was present. (Most often the result is a NULL value but not always.) The group by r.portfoliocode was not needed but even without it I still get the erroneous result set in SSRS. — Jay C
– Jay C, Commented Nov 21, 2013 at 20:56
If I remove table ll and all associates fields the query works in SSRS but I need the value in ll also which is from the same table as table l but I need to filter on a different field for that column. — Jay C
– Jay C, Commented Nov 21, 2013 at 20:57
Removing position date from the result set also makes it work in SSRS I would like to see this column though. I do need to filter on the value of it being NULL regardless of whether or not I display it. — Jay C
– Jay C, Commented Nov 21, 2013 at 21:02

Sam · Accepted Answer · 2013-11-22 11:35:03Z

1

I don't know the reason why your results are different. But I would suggest creating a sproc and using that.

Also, you may as well remove PositionDate from the select as you have the clause and PositionDate is NULL

answered Nov 22, 2013 at 11:35

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query working in SSMS but not in SSRS?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related