Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Concatenate NUMBER and VARCHAR issue

655717Jul 23 2012 — edited Jul 23 2012
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).
This post has been answered by chris227 on Jul 23 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 20 2012
Added on Jul 23 2012
4 comments
993 views