December 17, 2015 at 3:15 pm
This code runs fine in SSRS Query Designer.
IF @Company = 1
BEGIN
SELECT distinct [Gen_ Prod_ Posting Group], [No_] FROM [xxx].[dbo].[xxxx$Item]
WHERE [Gen_ Prod_ Posting Group] = @GPPG and [xxx].[dbo].[xxxx$Item].[Inventory Posting Group] = 2
END
IF @Company = 2
BEGIN
SELECT distinct [Gen_ Prod_ Posting Group], [No_] FROM [xxxx].[dbo].[s$Item]
WHERE [Gen_ Prod_ Posting Group] = @GPPG
END
But when I try to run it with the second filter as in the following it fails with "Conversion failed when converting the varchar value 'B' to data type int. The parameter @GPPG is set to text. What am I missing here?
Thank you!
IF @Company = 1
BEGIN
SELECT distinct [Gen_ Prod_ Posting Group], [No_] FROM [xxx].[dbo].[xxxx$Item]
WHERE [Gen_ Prod_ Posting Group] = @GPPG and [xxx].[dbo].[xxxx$Item].[Inventory Posting Group] = 2
END
IF @Company = 2
BEGIN
SELECT distinct [Gen_ Prod_ Posting Group], [No_] FROM [xxxx].[dbo].[s$Item]
WHERE [Gen_ Prod_ Posting Group] = @GPPG and [xxx].[dbo].[xxxx$Item].[Inventory Posting Group] = 2
END
December 17, 2015 at 3:23 pm
What's the datatype of [Inventory Posting Group]? What values are contained in it? Are any of them 'B'?
December 18, 2015 at 11:20 am
That’s the strange thing, there is no ‘B’ in Inventory Posting Group, only GPPG. If I run the Query without the Inventory Posting Group portion and select the ‘B’ as the GPPG filter it runs fine.
Inventory Posting Group datatype is text. I tried the Inventory Posting Group filter as like '2' and = 2 and = '2' - it doesn't matter - same error.
December 18, 2015 at 11:35 am
What is the data type for this column: [Gen_ Prod_ Posting Group]
If it is integer, your problem is allowing the @GPPG variable to contain values other pure numeric values. Data type precedence attempts to implicitly convert varchar values to integer values when comparing int values to varchar values. Unfortunately the character 'b' cannot be converted to an integer value which is why you are getting an error.
December 18, 2015 at 1:15 pm
OK - I found the problem but not the fix. It isn't resetting parameter values when the "Company" parameter changes. If I run it for Company 1 with the 'B' GPPG value, the query runs. If I switch to Company 2, the GPPG value doesn't reset and both the GPPG list and the Item list are still from Company 1!
That means there could be something amiss in the GPPG query which follows? That it's not "cascading?"
IF @Company = 1
BEGIN
SELECT DISTINCT G.[Gen_ Prod_ Posting Group]
FROM [dbo].[Company1$General Posting Setup] G
INNER JOIN [dbo].[Company1$Item] I
ON G.[Gen_ Prod_ Posting Group] = I.[Gen_ Prod_ Posting Group]
Where I.[Inventory Posting Group] = 'IC' or I.[Inventory Posting Group] = 'CHG'
END
IF @Company = 2
BEGIN
SELECT DISTINCT G.[Gen_ Prod_ Posting Group]
FROM [dbo].[Company2$General Posting Setup] G
INNER JOIN [dbo].[Company2$Item] I
ON G.[Gen_ Prod_ Posting Group] = I.[Gen_ Prod_ Posting Group]
Where I.[Inventory Posting Group] = 'IC' or I.[Inventory Posting Group] = 'CHG'
END
December 18, 2015 at 1:17 pm
I thought this at first, too. But GPPG is text with mixed values of 04,05,06,C,B,D - different set for each company.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply