November 13, 2014 at 10:17 am
Hi All,
I'm lost on what I'm doing wrong. My query successfully runs with a single parameter value, but not with multiple values. It may possibly be the case statement I have. Report Builder simply aborts and tells me it can't execute the query. No real details. Please have a look:
Text-based Query:
with JointMembers as
(
select
C1017.v_Member.MemberId
from
C1017.v_Member
where
C1017.v_Member.MemberId IN (@MemberIds) and
C1017.v_Member.[Date] = @DatabaseDate
Union
select
v_Account.MemberId
from
C1017.v_Account join
C1017.BridgeAccountMember on
BridgeAccountMember.AccountId = v_Account.Accountid and
BridgeAccountMember.[Date] = v_Account.[Date] join
C1017.v_Member on
v_Member.MemberId = BridgeAccountMember.MemberId and
BridgeAccountMember.[Date] = v_Member.[Date]
where
BridgeAccountMember.MemberId IN (@MemberIds) and
BridgeAccountMember.[Date] = @DatabaseDate
)
SELECT
C1017.v_Member.MemberNum
,C1017.v_Member.FullName
,C1017.v_Member.loanauthorized
,C1017.v_Member.MemberId
,Case when @MemberIds = C1017.v_Member.MemberId then ((@MemberIds * 2) + 1) else (@MemberIds *2) end as MainMember
FROM
C1017.v_Member join
JointMembers on
C1017.v_Member.MemberId = JointMembers.MemberId
WHERE
C1017.v_Member.[Date] = @DatabaseDate
ORDER BY
MainMember Desc
Thank you for your help!
Chad
November 13, 2014 at 10:22 am
THink about this:
You refer to @MemberIds in two different ways:
where
C1017.v_Member.MemberId IN (@MemberIds) and
-- and
@MemberIds = C1017.v_Member.MemberId
Now, if @MemberIds is a single value, this will work, but what if it contains multiple values? The second expression will fail.
Gerald Britton, Pluralsight courses
November 13, 2014 at 10:34 am
You're right, it does fail with multiple values. Is there a way I can fix it?
-- I don't think you provided a solution.
I can't use the 'In' in the second expression. I first thought that was my issue.
November 13, 2014 at 10:39 am
I would suggest turning your parameter into a table inside the sp. That way multiples will work.
November 13, 2014 at 12:32 pm
There are a couple of ways that come to mind for how to deal with this. The first is to pass a table valued parameter instead of a scalar value. The second option is to parse your list of MemberIDs. If you want to go with the second option, take a look at the article in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2014 at 1:18 pm
I'm confident that I'm using a 'table valued parameter' as my @MemberIds parameter 'gets values from a query" which is another dataset I've created. The second dataset gives me a nice list of memberids that I'm using with my first dataset. Maybe I'm wrong, as a 'table valued parameter' may be something else??
I'll read over the second option, but it looks fairly complicated...not sure if I'll be able to figure it out...
I'm open to other suggestions. 🙂
November 13, 2014 at 1:23 pm
You are using @MemberIds like a variable not a table.
November 13, 2014 at 1:24 pm
Chad.Taylor (11/13/2014)
I'm confident that I'm using a 'table valued parameter' as my @MemberIds parameter 'gets values from a query" which is another dataset I've created. The second dataset gives me a nice list of memberids that I'm using with my first dataset. Maybe I'm wrong, as a 'table valued parameter' may be something else??I'll read over the second option, but it looks fairly complicated...not sure if I'll be able to figure it out...
I'm open to other suggestions. 🙂
If it is already a table valued parameter then it isn't a delimited list. If it is a table valued parameter then you need to treat it like a table. Meaning you select from it, join to it etc. What is the datatype for @MemberIds?
I just now realized this is in a report. I can barely spell SSRS let alone actually use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2014 at 1:39 pm
I apologize as I'm learning.. It's taking me a bit to understand what you're saying.
Yes, it's not a delimited list. I created a separate visible table or tablix that shows me the list of MemberIds. Each one is a six digit value such as 546299. I believe that's considered an integer.
I do have 'select' and 'join' in the areas I believe they need to be.
Also, yes - I am creating a report in Microsoft SQL Report Builder 2008 R2.
November 13, 2014 at 1:45 pm
djj - how would I use @MemberIds like a table instead of a variable?
I'm not sure what you mean...
I really appreciate the help 😀
November 13, 2014 at 1:55 pm
I would recommend you use aliases in your queries. I had not until now really looked at your query. It is difficult to decipher when every column is referenced by the full two part name. Here is a modification to your query using aliases. Notice how much easier this is to read.
with JointMembers as
(
select m.MemberId
from C1017.v_Member m
join @MemberIds mi on mi.MemberId = m.MemberId
where m.[Date] = @DatabaseDate
Union
select a.MemberId
from C1017.v_Account a
join C1017.BridgeAccountMember bam on bam.AccountId = a.Accountid
and bam.[Date] = a.[Date]
join C1017.v_Member m on m.MemberId = bam.MemberId
and bam.[Date] = m.[Date]
join @MemberIds mi on mi.MemberId = bam.MemberId
where bam.[Date] = @DatabaseDate
)
SELECT m.MemberNum
, m.FullName
, m.loanauthorized
, m.MemberId
, Case when jm.MemberId = m.MemberId then (jm.MemberId * 2) + 1 else jm.MemberId * 2 end as MainMember
FROM C1017.v_Member m
join JointMembers jm on m.MemberId = jm.MemberId
WHERE m.v_Member.[Date] = @DatabaseDate
ORDER BY MainMember Desc
Now as I said previously I now virtually nothing about SSRS so I decided I would look up how to use multi valued parameters within SSRS. In other words, are they passed as a delimited string or as a table. I searched for very generic "using multi valued parameter ssrs". The very first article that popped up mentions that they are a delimited string. It even goes so far as to provide a code sample. The funny part is there is a link to the same article I mentioned earlier about splitting strings.
Here is the search link. https://www.google.com/search?q=using+multi+valued+parameter+ssrs&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a&channel=nts
And the first article that comes up...http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2014 at 2:27 pm
I notice a couple changes (see highlighted parts below). Any reason to change them?
Running the query with these changes now doesn't work for a single MemberId value (like it did before).
Aliases are better. 😀 I'll start reading the url you gave me. Thanks!
Sean Lange (11/13/2014)
I would recommend you use aliases...
with JointMembers as
(
select m.MemberId
from C1017.v_Member m
[highlight=#ffff11]join @MemberIds mi on mi.MemberId = m.MemberId
where m.[Date] = @DatabaseDate[/highlight]
Union
select a.MemberId
from C1017.v_Account a
join C1017.BridgeAccountMember bam on bam.AccountId = a.Accountid
and bam.[Date] = a.[Date]
join C1017.v_Member m on m.MemberId = bam.MemberId
and bam.[Date] = m.[Date]
[highlight=#ffff11]join @MemberIds mi on mi.MemberId = bam.MemberId[/highlight]
where bam.[Date] = @DatabaseDate
)
SELECT m.MemberNum
, m.FullName
, m.loanauthorized
, m.MemberId
, Case when jm.MemberId = m.MemberId then (jm.MemberId * 2) + 1 else jm.MemberId * 2 end as MainMember
FROM C1017.v_Member m
join JointMembers jm on m.MemberId = jm.MemberId
WHERE m.v_Member.[Date] = @DatabaseDate
ORDER BY MainMember Desc
Now as I said previously I now virtually nothing about SSRS so...
November 13, 2014 at 2:29 pm
or look at the two areas I at least, tried to highlight...:w00t:
November 13, 2014 at 2:34 pm
Chad.Taylor (11/13/2014)
or look at the two areas I at least, tried to highlight...:w00t:
Yes the query I posted will not work because the multi valued parameter is NOT a table. It was an example of how your query might look and I should have put that back to the same code you had to avoid confusion. As I said in my previous response this was because I was not sure how they are handled in SSRS. You will have to parse your delimited values and it will work exactly the same with one or more values being passed in.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply