This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
kaustubh sharma wrote:select case casedetails.[re-openindicator] when 1 then 'Yes' when 0 then 'No' else casedetails.[re-openindicator] end from casedetails
If the bit is neither 0 not 1 (this will not happen but the SQL engine cannot see that) the returned column will still be of type bit. Instead of converting this into a varchar the "Yes" and "No" are attempted to be converted back into a bit.
The following two would solve this:
1) select case casedetails.[re-openindicator] when 0 then 'No' else 'Yes' end from casedetails -- anything that isn't 0 will be treated as "Yes"
2) select case casedetails.[re-openindicator] when 1 then 'Yes' when 0 then 'No' else cast(casedetails.[re-openindicator] as varchar) end from casedetails -- convert the bit into a varchar
Now I am aware that you are probably not in control of the generated SQL queries, but you must find a way to let Jasper use either SQL statement.