| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2010-04-29 : 03:27:13
|
| SELECT UserName,RequirementsFROM HC_USERS WHERE HC_USERS.RID in (cast (@rid)as bigint)This query i have used in my report. it is giving conversion error. @rid has multiple values so i have declared it as varchar and converting it to bigint (col datatype) to compare with the corresponding column.but i am getting conversion error. please help. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-29 : 04:03:07
|
What you need is a table valued function so you can join each value of your array.See here for example:http://www.sommarskog.se/arrays-in-sql-2005.html
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-29 : 04:10:59
|
| Thanks webfred. i have a table valued function with me. now if i use it in the main report, i get the following error:The Value expression for the query parameter @locid contains error:[BC30201] Expression expected.here, @locid is of type integer and is a single value param. the table valued function is used on @rid which is of type integer and is multi-valued. this rid is a bigint col in table.is it causing error bcoz of tht? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-29 : 05:15:39
|
| Thanks for the reply.I have done everything in the same way as mentioned in the link. But still it is not working correctly.My master report has multiple value parameter @rid.On click on this rid you will be navigated to the detail. in detail you will be seeing only one record (the one you chose in master).So i added the table valued func to the rid OF MASTER and NOT IN DETAIL. now i am agian getting CONVERSION ERROR.please help me. i need to solve this today :( |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-29 : 06:14:02
|
| Post your code. We can't debug what we can't see.--Gail ShawSQL Server MVP |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-29 : 06:18:07
|
| This is the SP i am using in Detail report:ALTER PROC [dbo].[sp_UserPerformance] (@locid bigint,@rid bigint,@fromdate datetime,@todate datetime)ASBEGINSelect rid,UserName as 'User Name' ,isnull(( Select Sum(HC_REQ_TEAM.Position) From HC_REQ_TEAM WITH(NOLOCK) WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID AND HC_REQ_TEAM.ReqID in(Select RID From HC_REQUISITIONS WITH(NOLOCK) WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 )) and HC_REQ_TEAM.ReqID in(Select ReqID From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK) WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate) and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings From HC_USERS WITH(NOLOCK) WHERE HC_USERS.RID in(@rid) AND HC_USERS.Locationid in (@locid) Group By HC_USERS.RID,HC_USERS.UserNameENDThe @rid will be from MASTER which lists the USerName. |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-29 : 06:18:37
|
| Is the issue related to datatype ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-29 : 07:22:34
|
| How can the @rid parameter be a comma-delimied list? It's declared as type bigint.--Gail ShawSQL Server MVP |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-29 : 07:25:56
|
| It is a comma delimited list in master. the above query is of detail.only first value is passed to the parameter.actually values to the subreport must be passed only after clicking the username. and only one username can be clicked at a time to view detail... |
|
|
|
|
whats the error?