Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to Convert type in sql or jasper i report

 
Kaustubh Sharma
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi .......

in my sql query i want to change bit type 0 or 1 in values 'Yes' or 'No' but while runnig query below I am getting

Error: Conversion failed when converting the varchar value 'No' to data type bit.

Query:select case casedetails.[re-openindicator] when 1 then 'Yes' when 0 then 'No' else casedetails.[re-openindicator] end from casedetails

Well I am also passing this query to Jasper I report but there also it is converting it into boolean values false or true but I want it as Yes or No...


looking for a sharp Brain to solve my problem ..... please help me out to convert it any side(jasper i report or in sql query)

Thanks in Advance

Kaustubh
 
Kaustubh Sharma
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator




This is the code in my jasper i report
 
Rob Spoor
Sheriff
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kaustubh Sharma
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is working Rob ..You Rock Man. You are My Hero. .......
Beer for all..

Thanks Buddy

Kaustubh
 
Rob Spoor
Sheriff
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic