CODEWITHSUNDEEP
sqlderby
Manoj
Manoj

Reputation: 329

Convert CHAR from INTEGER in java derby DB

In following query want to give integer value in else part. If i give, error is: Error code -1, SQL state 42X89: Types 'CHAR' and 'INTEGER' are not type compatible. Neither type is assignable to the other type.

select case
when ID = 1 then 'Issue'
when ID = 2 then 'Reload'
when ID = 3 then 'Redeem'
else ID
end
from TXN
where CARDNO = '10001'

how can i do it?

Upvotes: 2

Views: 5520

Answers (3)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Just CAST that ID to VARCHAR()

select case
when ID = '1' then 'Issue'
when ID = '2' then 'Reload'
when ID = '3' then 'Redeem'
else CAST(ID AS VARCHAR(2))
end
from TXN
where CARDNO = '10001'

Upvotes: 0

Kin Ho YEUNG
Kin Ho YEUNG

Reputation: 1

cast to char is fine, but it will have all the space padding, so after cast to char you then have to cast the char to varchar and then RTRIM.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

Cast to CHAR instead of VARCHAR:

select case
when ID = 1 then 'Issue'
when ID = 2 then 'Reload'
when ID = 3 then 'Redeem'
else CAST(ID AS CHAR(10))
end
from TXN
where CARDNO = '10001'

Compatible types for cast: http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html

Upvotes: 1

Related Questions