you are right. Both the sides have the same collation which is UCS_BASIC since result of CAST picks up the DTD associated with what the operand is getting CASTed to. Since char(10) has collation type of UCS_BASIC attached to it, the result of CAST picks up that collation irrespective of collation of the operand. I am wondering though if that is what user would expect when writing a query like following select * from customer where CAST(? as char(10)) = cast(name as char(10)). That is UCS_BASIC getting used for comparison rather than territory based collation.
Mamta On 6/5/07, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Mamta Satoor wrote: > I realize that we are still discussing what should be the right > collation for different character string type instances, but I want to > put out cases which will hopefully aid in making a decision or expose > different scenarios. > > Consider following case for parameter with CAST. > select * from customer where CAST(? as char(10)) = name > The CAST node will assign the DTD associated with char(10) to the ?. But > what should be the collation type for char(10). Currently in the code, > it is UCS_BASIC and hence ? ends up getting UCS_BASIC. As a result, the > result of the CAST operand gets a collation type of UCS_BASIC amd the > comparison against "name" fails with compilation time error. Now doing a > CAST on "name" is not an option to fix this problem because CAST(name as > char(10)) will result in a character string type with collation of > territory based. So, the question is what should be the collation type > of char(10) in the eg above? Why would the two casts be different here? What makes one UCS_BASIC and the other territory based? Dan.
