We are unfortunately not in control of the source and destination tables.
In the source table the 'quantity' column is numeric. In the destination table 'quantityDest' column is a string. So the question is how can we convert from numeric value to string value using some KSQL syntax or function. We tried CAST(quantity as string) but that yields a string value like this: '0,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,375.000' instead of something like this: '375.000' Regards Rainer > On 08 Sep 2020, at 11:20, Robin Moffatt <ro...@confluent.io> wrote: > > Why is quantity a STRING? Should it be a numeric? > > > -- > > Robin Moffatt | Senior Developer Advocate | ro...@confluent.io | @rmoff > > > On Tue, 8 Sep 2020 at 08:52, Rainer Schamm <rai...@lsd.co.za> wrote: > >> 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 >> >> >> >> >> >>
