March 20, 2018 at 8:13 am
I'm running the code in ssrs that has sql server data source connection.
Following is the code snippet of my query,
How can I convert the line that is underlined and bold into char in sql server. I also used
convert(varchar, grp.Data_Group_Number) in @Data_Group_Number or '0' in @Data_Group_Number)
but I'm getting error.
declare @Data_Group_Number varchar
set @Data_Group_Number = 0
with group_1 as
(select 1 Group_Number, 1 Data_Sort
union select 2 Group_Number, 2 Data_Sort
union select 3 Group_Number, 3 Data_Sort),
data_group as
(select Case when grp.Group_Number = 1 Then 'Company1'
When grp.Group_Number = 2 Then 'Company2'
When grp.Group_Number = 3 Then 'Company3'
End Data_Group
from group_1 grp
where (cast(grp.Group_Number) in @Group_Number or '0' in @Group_Number)
)
select *from data_group;
March 20, 2018 at 8:26 am
Your syntax for CAST is wrong.
It's CAST(expression AS datatype)
for example...
SELECT 'The film, '+ Title + ' is ' + CAST(RunTimeMinutes AS VARCHAR(3)) + ' minutes long.'
FROM dbo.Film;
and wouldn't you do the CAST in the last SELECT statement, not in the intermediate ones?
March 20, 2018 at 9:02 am
I've changed my code, but still getting error "Incorrect syntax near '@Group_Number'
Declare @group_number varchar
Set @group_number = 0;
With group_1 as
( select 1 Group_Number, 1 Data_Sort fromdual
union select 2 Group_Number, 2Data_Sort from dual
union select 3 Group_Number, 3 Data_Sortfrom dual
)
select Case When cast(grp. Group_NumberAS varchar(2)) = 1 Then 'Company1'
When cast(grp. Group_NumberAS varchar(2)) = 2 Then ‘Company2’
When cast(grp.Data_Group_NumberAS varchar(2)) = 3 Then ‘Company3
End Data_Group,
From rpt_groupgrp
Where grp. Group_Numberin @Group_Number or '0' in @Group_Number
March 20, 2018 at 9:21 am
'dual' is a table in Oracle. Are you using Oracle as your source or SQL Server?
March 20, 2018 at 9:33 am
I'm sorry while typing in the post I accidently typed dual. I'm not using dual in my code.
March 20, 2018 at 10:21 am
soldout6000 - Tuesday, March 20, 2018 9:02 AMI've changed my code, but still getting error "Incorrect syntax near '@Group_Number'
Declare @group_number varchar
Set @group_number = 0;
With group_1 as
( select 1 Group_Number, 1 Data_Sort fromdual
union select 2 Group_Number, 2Data_Sort from dual
union select 3 Group_Number, 3 Data_Sortfrom dual
)
select Case When cast(grp. Group_NumberAS varchar(2)) = 1 Then 'Company1'
When cast(grp. Group_NumberAS varchar(2)) = 2 Then ‘Company2’
When cast(grp.Data_Group_NumberAS varchar(2)) = 3 Then ‘Company3
End Data_Group,
From rpt_groupgrp
Where grp. Group_Numberin @Group_Number or '0' in @Group_Number
The where clause is incorrect. For the first part, something like that with an in clause would normally be something like: Where grp.Group_Number in (@Group_Number)
and I have no idea what you are trying to do in the second part. @Group_Number you declared as varchar and no size. So it's essentially varchar(1).
You aren't doing anything with @group_number other than setting it to 0 after the declaration so the where clause doesn't make a lot of sense. I am guessing there could be more than just syntax errors that is problematic.
Sue
March 20, 2018 at 10:22 am
Sue_H - Tuesday, March 20, 2018 10:21 AMThe where clause is incorrect. For the first part, something like that with an in clause would normally be something like:
Where grp.Group_Number in (@Group_Number)
and I have no idea what you are trying to do in the second part. @Group_Number you declared as varchar and no size. So it's essentially varchar(1).
You aren't doing anything with @group_number other than setting it to 0 after the declaration so the where clause doesn't make a lot of sense. I am guessing there could be more than just syntax errors that is problematic.Sue
And the IN clause won't work as expected if it has more than one value.
March 20, 2018 at 10:28 am
Lynn Pettis - Tuesday, March 20, 2018 10:22 AMSue_H - Tuesday, March 20, 2018 10:21 AMThe where clause is incorrect. For the first part, something like that with an in clause would normally be something like:
Where grp.Group_Number in (@Group_Number)
and I have no idea what you are trying to do in the second part. @Group_Number you declared as varchar and no size. So it's essentially varchar(1).
You aren't doing anything with @group_number other than setting it to 0 after the declaration so the where clause doesn't make a lot of sense. I am guessing there could be more than just syntax errors that is problematic.Sue
And the IN clause won't work as expected if it has more than one value.
Check out that declaration - It can only have a single character. So don't even need the in. Just doesn't make much sense and can't figure out what they may be trying to accomplish.
Sue
March 20, 2018 at 10:46 am
Sue_H - Tuesday, March 20, 2018 10:28 AMLynn Pettis - Tuesday, March 20, 2018 10:22 AMSue_H - Tuesday, March 20, 2018 10:21 AMThe where clause is incorrect. For the first part, something like that with an in clause would normally be something like:
Where grp.Group_Number in (@Group_Number)
and I have no idea what you are trying to do in the second part. @Group_Number you declared as varchar and no size. So it's essentially varchar(1).
You aren't doing anything with @group_number other than setting it to 0 after the declaration so the where clause doesn't make a lot of sense. I am guessing there could be more than just syntax errors that is problematic.Sue
And the IN clause won't work as expected if it has more than one value.
Check out that declaration - It can only have a single character. So don't even need the in. Just doesn't make much sense and can't figure out what they may be trying to accomplish.
Sue
I have checked out the declaration. I simply stated that the IN clause as you posted won't work if that same variable has multiple values.
March 20, 2018 at 11:24 am
Thank you so much for the feed back.
I did as follows;
Where grp.Group_Number in (@Group_Number)
and it's working.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply