Select dynamic string has a different value when referenced in Where clause
- by David
I dynamically select a string built using another string. So, if string1='David Banner', then MyDynamicString should be 'DBanne'
Select
...
, Left(
left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word
,1) //first character
+ (select top 1 strval from dbo.SPLIT(string1,' ')
//second word
where strval not in (select top 1 strval from dbo.SPLIT(string1,' ')))
,6) //1st character of 1st word, followed by up to 5 characters of second word
[MyDynamicString]
,...
From table1 Join table2 on table1pkey=table2fkey
Where MyDynamicString <> table2.someotherfield
I know table2.someotherfield is not equal to the dynamic string. However, when I replace MyDynamicString in the Where clause with the full left(left(etc.. function, it works as expected.
Can I not reference this string later in the query? Do I have to build it using the left(left(etc.. function each time in the where clause?
