asked
Try_cast conversion issue
Hello All, I'm facing a wired issue. It would be great if some one help me on this. This query worked like a charm until i add where clause with IS NULL . If i execute with Is null i got below error (With IS not Null query is working fine). When i execute the inner query i got the desired result set invalid records with null values and Valid records has been casts properly. But when i try to filter it in outer query it fails that too because of few records. Doubts: In inner query all invalid records has been converted to null i could see that. But why it fails when it comes to outer query that too only because of few records (i found this by executing invalid records one by one)? Is that issue with Try_cast? select Number,content from (select Name, Number,TRY_cast(TRY_CAST(TRY_CAST((Content) AS VARBINARY (MAX)) AS XML) as nvarchar (max)) as content from Documents where LTRIM(RTRIM(NAME)) in ('XYZ.xmll') ) A where Content is null Error: Warning: Fatal error 7102 occurred at Jun 7 2016 4:47PM. Note the error and time, and contact your system administrator. Thanks Rathna
Does the query work without TRY_CAST? select Number , content from ( select Name , Number , Content from Documents where ltrim(rtrim(NAME)) in ( 'XYZ.xmll' ) ) A where content is null;
OK. So what about moving the is null inside the inner query select Number , content from ( select Name , Number , try_cast(try_cast(try_cast(( Content ) as varbinary(max)) as xml) as nvarchar(max)) as content from Documents where ltrim(rtrim(NAME)) in ( 'XYZ.xmll' ) and content is null ) A ;
OK so now we know it is the try_cast (don't forget I'm doing this blind here :) ) but I still think it's to do with your data. The limited testing I've done and your query works fine, the only difference is I don't have your data :(
I Appreciate your effort Kev Riley. TRY_CAST has converted the invalid to null. I could see that in result set. My doubt is like is there any chance that Try_cast may returning null value without EOF. I got this doubt when i do the Name with order by. i took two records NAME AA - Valid record BB- Invalid record When i do order by ASC it works fine. But if i do order by DESC it through error. I have three record. NAME AA- Valid record BB- Invalid record CC- Valid record I not able do Order by in ASC or DESC. Hope This will narrow down my problem. Below is my inner query result for invalid record NAME, Try_Cast_Fun AM, (null) Thanks Rathna
17 People are following this question.
