KSQL Enrichment issue with converting decimal to string column

[Rainer Schamm]

Hi all

we are struggling a bit with a fairly simple stream that is supposed to map 
columns from a topic populated by a Source Jdbc Connector into a format 
destined for a Sink Jdbc Connector.

Our tables look something like this:
(These are not the actual column names we are using though)


SOURCE TABLE (DB2)
---------------------------------
name CHAR
street CHAR
quantity NUMERIC           <<< decimal type in source avro

DEST TABLE (Oracle)
------------------------------
nameDest VARCHAR2
streetDest VARCHAR2
quantityDest VARCHAR2   <<< string type in sink avro

The problem we are facing is how to cleanly convert the numeric quantity value 
into a varchar2 value for the quantityDest.

CREATE STREAM SOURCE_TABLE_MAPPED AS
    SELECT
        name as nameDest,
        TRIM(street) AS streetDest,
        quanity AS quanityDest,
    FROM SOURCE_TABLE
EMIT CHANGES;

I can't seem to find any scalar function that allows us to format the decimal 
value to a string, e.g. String.format(„%.2f“, quantiity)

I have tried using a CAST like this: CAST(quantity as string); but the 
generated string looked very strange and was way too long.

So in a nutshell, how can I fix this line:

        quanity AS quanityDest,

To convert the quantity field to a quantityDest string field.

As a side note, we are using Avro schemas on the topics of both the source and 
sink side.

Thanks in advance to anyone who can give a suggestion in the right direction:)

Regards
Rainer