February 22, 2009 at 10:55 am
We've just upgraded the server from 2000 to 2008.
I'm having problems with this query on 2005 & 2008.
select
p.Module,
pd.Currency,
convert(smallint, substring(pd.promo_code, 5, len(rtrim(pd.promo_code))-6)) [Issue],
sum(1) [ERenewalRcvQty],
sum(pd.Amt_Paid) [ERenewalRcvAmount]
from dbo.promo_details pd
INNER JOIN dbo.Publications p ON pd.PubCode = p.PubCode
INNER JOIN dbo.Modules m ON m.Module = p.Module
INNER JOIN dbo.IssueXRef x ON x.Module = m.Module and x.IssueNumber between m.CurrentIssue-11 and m.CurrentIssue
INNER JOIN dbo.ModuleProcesses mp ON p.Module = mp.Module and mp.Process = @Process
where pd.Promo_Code in (
select promo_code
from dbo.promo_codes
where rpt_code = '0026'
and left(promo_code, len(promo_code)-1) in (
select promo_code
from dbo.promo_codes
where rpt_code = '0104'))
and pd.Promo_Code like m.Module + 'ER' + convert(varchar(5), x.IssueNumber) + '%'
group by p.Module, pd.Currency, convert(smallint, substring(pd.promo_code, 5, len(rtrim(pd.promo_code))-6))
order by p.Module, convert(smallint, substring(pd.promo_code, 5, len(rtrim(pd.promo_code))-6)), pd.Currency
The problem is that I'm getting an error : "Conversion failed when converting the varchar value '50A' to data type smallint."
Okay, so there's only one conversion going on, but it's happening in three places. So I modified the query to be a CTE so that I can easily experiment with it.
;with TmpTbl AS (
select
p.Module,
pd.Currency,
pd.promo_code,
pd.Amt_Paid
from dbo.promo_details pd
INNER JOIN dbo.Publications p ON pd.PubCode = p.PubCode
INNER JOIN dbo.Modules m ON m.Module = p.Module
INNER JOIN dbo.IssueXRef x ON x.Module = m.Module and x.IssueNumber between m.CurrentIssue-11 and m.CurrentIssue
INNER JOIN dbo.ModuleProcesses mp ON p.Module = mp.Module and mp.Process = @Process
where pd.Promo_Code in (
select promo_code
from dbo.promo_codes
where rpt_code = '0026'
and left(promo_code, len(promo_code)-1) in (
select promo_code
from dbo.promo_codes
where rpt_code = '0104'))
and pd.Promo_Code like m.Module + 'ER' + convert(varchar(5), x.IssueNumber) + '%'
)
select
t.Module,
t.Currency,
CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)) [Issue]
,SUM(1) [ERenewalRcvQty]
,SUM(t.amt_paid) [ERenewalRcvAmount]
from TmpTbl t
group by t.Module, t.Currency, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6))
order by t.Module, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)), t.Currency
If I remark out the two sum operators, and the group by, the query runs just fine. If I put the group by statement back in, I get the same error.
Next, I tried putting the results into a temp table, and running the query off of that:
declare @TmpTbl TABLE (Module char(2), Currency varchar(25), promo_code char(10), Amt_Paid decimal(8,2))
--;with TmpTbl AS (
insert into @TmpTbl (Module, Currency, promo_code, Amt_Paid)
select
p.Module,
pd.Currency,
pd.promo_code,
pd.Amt_Paid
from dbo.promo_details pd
INNER JOIN dbo.Publications p ON pd.PubCode = p.PubCode
INNER JOIN dbo.Modules m ON m.Module = p.Module
INNER JOIN dbo.IssueXRef x ON x.Module = m.Module and x.IssueNumber between m.CurrentIssue-11 and m.CurrentIssue
INNER JOIN dbo.ModuleProcesses mp ON p.Module = mp.Module and mp.Process = @Process
where pd.Promo_Code in (
select promo_code
from dbo.promo_codes
where rpt_code = '0026'
and left(promo_code, len(promo_code)-1) in (
select promo_code
from dbo.promo_codes
where rpt_code = '0104'))
and pd.Promo_Code like m.Module + 'ER' + convert(varchar(5), x.IssueNumber) + '%'
--)
select
t.Module,
t.Currency,
CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)) [Issue]
,SUM(1) [ERenewalRcvQty]
,SUM(t.amt_paid) [ERenewalRcvAmount]
from @TmpTbl t
group by t.Module, t.Currency, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6))
order by t.Module, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)), t.Currency
Now this works fine.
So, what's going on with the first two queries where the group by is causing a conversion error when the query runs fine with the conversion everywhere else?
Is this a bug of some sort where the group by is looking at records that are excluded from the query from the where condition? If so, then this is a problem in 2005 and 2008.
I have a work-around for my problem, but I'd prefer not to have that intermediate temp table.
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2009 at 4:48 pm
When dealing with CTE's, keep in mind that you aren't really specifying a sequence of steps, it just reads that way. The optimizer treats it all as one query, and might be making a decision that it's faster to do a GROUP BY on all values, then just filter the ones specified in your where clause.
That's my theory anyway, but I can't see your execution plan.
Obviously you are getting an error because the value '50A' is being passed to this string:
convert(smallint, substring(pd.promo_code, 5, len(rtrim(pd.promo_code))-6))
I presume that '50A' isn't valid for your purposes, or you wouldn't be trying to turn it into a smallint, so a bandaid fix would be to add a where clause to your final query to make sure all substrings you are trying to cast are numeric.
An example follows; but if I were you, I would be digging into my data and finding out why a value of '50A' is being returned when '50' is expected.
Please let me know if this helps.
Bob
select
t.Module,
t.Currency,
CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)) [Issue]
,SUM(1) [ERenewalRcvQty]
,SUM(t.amt_paid) [ERenewalRcvAmount]
from TmpTbl t
-------------------------------------------------------------------------------
WHERE isnumeric(substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)) = 1
-------------------------------------------------------------------------------
group by t.Module, t.Currency, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6))
order by t.Module, CONVERT(smallint, substring(t.promo_code, 5, len(rtrim(t.promo_code))-6)), t.Currency
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 23, 2009 at 8:26 am
Bob Hovious (2/22/2009)
When dealing with CTE's, keep in mind that you aren't really specifying a sequence of steps, it just reads that way. The optimizer treats it all as one query, and might be making a decision that it's faster to do a GROUP BY on all values, then just filter the ones specified in your where clause.
I changed it to a CTE to make it easier to test... for instance, do a select * from the cte table to see what all it's retrieving. And everything that it's retrieving passes the conversion.
As my work-around shows, I can put the detailed data into a temp table, and then run a second select with the group by off of that temp table to aggregrate and I get the proper results. However, if I put the group by on the main query, the error is thrown. I can select and order by with the convert, but when I throw the group by into the mix, then I get the error.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply