I'm trying to concatenate an integer and a string. However, the number that i'm trying to concatenate is the a number returned from the rank function and it's within the context of a CASE statement. I realize that sounds a bit confusing, so here's a psuedo example of what I'm trying to do:
WITH temp_table AS
(
SELECT 1 AS col1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 3 FROM DUAL
)
SELECT CASE WHEN COUNT(*) OVER (PARTITION BY col1) > 1
THEN
RANK() OVER (ORDER BY col1) || 't'
ELSE
RANK() OVER (ORDER BY col1)
END AS test
FROM temp_table;
Desired result set:
TEST
1
2
3t
3t
When I do the above, I get a "inconsistent datatypes: expected CHAR got NUMBER" exception. I tried converting the "RANK() OVER (ORDER BY col1) " to a string via the TO_CHAR function and CAST function, but neither worked. I did figure out how to successfully achieve my desired result by creating a subquery, converting the test column to a char, and than appending the 't' character to it in the outer SELECT; i was wondering if it was possible to do what I have requested in one query (without a subquery).