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 > > > > > >
