You were correct on doing a sanity check...The query works fine in my PROD environment, but was not working over in my QA environment. Odd thing is I literally just created this table with the DDL from PROD, but I think something got fouled up with the data copy over to QA. It works fine now since I recreated the table and reloaded the data.
However - I now have a new problem...I need to convert that column to a DECIMAL(2,1) with ROUND precision. I am getting close using CAST twice, but I'm getting errors with the alias naming. It looks like this right now:
CAST(CAST([extendedinfo].value('(/ExtendedInfo/Fragmentation)', 'VARCHAR(MAX)') + '%' AS 'extendedinfo' AS DECIMAL(2,1))),
WHEN command LIKE '%REORGANIZE%' THEN 'REORGANIZE'
WHEN command LIKE '%REBUILD%' THEN 'REBUILD'
The error states Incorrect syntax near "extendedinfo". Expecting DOUBLE, ID, NATIONAL, or QUOTED_ID.
I have tried to close the 'extendedinfo' column alias with double quotes, parens, brackets, but thus far - no luck in clearing this error. Perhaps I am going about this the wrong way? I am just trying to convert it to DECIMAL right now, and will address the ROUNDing there after. I have also tried using CONVERT but have had even less luck there. If I need to take this over to another forum specific to T-SQL, I will do that as well (just let me know).