My requirement is to prefix a ’0′ (Zero) to the number, if the number falls below 10, i.e. I should always get two digits as my result when I fetch the data from the database. So, I wrote a SQL query to do the same. Below is the sample query.
SELECT CASE WHEN FLD < 10 THEN '0'||FLD ELSE FLD END FROM SOMEDB
Note that the FLD is a numeric field. As you see, the above query should return ’01′, if the FLD has value less than 10. But to surprise, the query was always returning the FLD value without prefixing the ’0′ even if FLD falls lesser than 10. I really had no idea of why it was happening like that. Then after doing some trial-and-error debugging, found out the root cause.
The issue is with the ELSE part of the above query. The ELSE part returns the FLD value as it is. Since this FLD is a numeric field, somehow it is converting the entire output to numeric. This is why even if the FLD value falls below 10 and prefixed with ’0′ to become ’01′, finally it is converted to a numeric value, resulting the prefix ’0′ to go away. Finally I was getting the single digit FLD value. So to fix the issue, I’ve changed the query as below:
SELECT CASE WHEN FLD < 10 THEN '0'||FLD ELSE CHAR(FLD) END FROM SOMEDB
As you see, I am converting the FLD value to CHAR in the ELSE part. This solved the issue and finally my query was working fine!
