I am trying to use the SUBSTR function to combine a number field with the first 3 digits of that same number field so that the first 3 digits are repeated. I have no problem using the following code...
SUBSTR(d.ID_NO, 1, 3) + d.ID_NO
I am struggling now to cast that so that the ouput is a text field vs a numeric... I tried the following but am getting the numeric overflow error.
CAST(CAST(SUBSTR(d.ID_NO, 1, 3)+(d.ID_NO) AS INTEGER) AS CHAR(1))
If you could provide some input data and expected output it would be helpful.
I can't see the link between your need and the code so far.
The error is because substr(something, 1, 3) returns 3 characters, and later you try to cast that into 1 character, so it just doesn't fit.
That makes sense. An example of the numeric ID_NO field looks something like this '68012345678912'. I'm trying to duplicate the first 3 digits so that it looks like '68068012345678912' and becomes 17 digits. I would also like the field output to to convert from number to text.
Ok, note the concatenation operator is || in SQL, I think only SQL-Server and Sybase use + as a concatenation operator.
Try this code :
create multiset volatile table mvt_tab, no log ( mvt_pi byteint , col_num bigint ) primary index (mvt_pi) on commit preserve rows; insert into mvt_tab values (0, 68012345678912); select col_num , cast(col_num as varchar(14)) as col_chr , substring(col_chr from 1 for 3) || col_chr as col_chr_concat , type(col_chr_concat) from mvt_tab where mvt_pi = 0;