Hello,
When I try to run the following query on two different SQL Servers,
I get error on one of the server (both has same set of data). I was
trying to get rows for ProductCode='XYZ_Q1'.
SELECT ProductName, ProductType, ProductDesc FROM Product WHERE
DepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,
CHARINDEX('_', ProductCode)+2, 1) AS int)=1
Example data
ProductCode|ProductName|ProductType|DepartmentID|P roductDesc
XYZ_T_1|Test1|1000|12|Test
XYZ_T_2|Test2|1000|12|Test
ABC_T_1|Test3|1000|11|Test
ABC_T_2|Test4|1000||11|Test
XYZ_Q1|Test5|1000|12|Test
ABC_Q1|Test6|1000|11|Test
It's trying to cast all values under 'ProductCode' column instead of
applying to subset with condition 'DeparmentID=12 AND ProductType >
2000 '
I solved the problem by equating it to whole string rather than
trying to extract the integer part of it. But I wanted find-out reason
as to why this is happenning.
Is there any SQL Server setting that's causing this?
Thank you very much
Manchaiah