April 10, 2012 at 5:37 am
Hello
I've resolved the issue now, but I thought I'd mention it and see if anyone has any ideas on why this could be caused.
I've got a query below:
DECLARE @STR nvarchar(4000)
SET @STR = ''
SELECT top 22
CASE
WHEN a1.at_style = 'Numeric' THEN ISNULL(CAST(atv_NumericValue AS VARCHAR(100)),'') + ISNULL(a2.at_short,'') + ' ' + a1.at_short
WHEN a1.at_style = 'Alphanumeric' THEN ISNULL(CAST(atv_TextValue AS VARCHAR(100)),'') + ISNULL(a2.at_short,'') + ' ' + a1.at_short
WHEN a1.at_style = 'Set Value' THEN a1.at_short
ELSE 'xxx'
END
FROM attribute_value
INNER JOIN vw_attribute a1 ON a1.at_lang = 'GB' AND atv_at_id = a1.at_id
LEFT JOIN vw_attribute a2 ON a2.at_lang = 'GB' AND a1.at_unit_id = a2.at_id
WHERE atv_vv_id = 1537
AND atv_atvt_id = 1
ORDER BY atv_DisplayOrder
SELECT @STR
This would output '4 x 45 spdMTM', however if I changed top 22 to top 23 it would incorrectly return 'MTM'. There are only three results in the result set.
This could be resolved by removing the + ISNULL(a2.at_short,'') from all lines, or by removing either the Numeric line or the Alphanumeric line.
I tried wrapping ISNULL or Coalesce around everything, and added in Else to all the case statements however nothing resolved.
I looked at the Query Execution plan for both Top 22 and Top 23, they were the same except for at the end one had Sort then Compute Scalar and the other had them around the other way.
In the end I removed all statistics for all of the underlying tables and it resolved the issue.
Does anyone have any idea what could of caused this? Or how I can stop it from happening again the in future.
Thanks
Jamie
April 17, 2012 at 1:44 am
Guess not then 🙁
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply