Archives of the TeradataForum

Message Posted: Tue, 20 Feb 2007 @ 18:24:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Can anyone help with the following SUBSTR issue I have.

When I run the following select using SUBSTR, it works fine and returns the rows I expect:

     select * from TAB1
     WHERE substr (mst_fn, 1, index (mst_fn, '-') -1) =
     substr (gst_fn,(index (gst_fn, '-')) +1)
     and substr (gst_fn, 1, index (gst_fn, '-') -1) =
     substr (mst_fn,(index (mst_fn, '-')) +1)
     and index (mst_fn, '-') > 0
     and index (gst_fn, '-') > 0
     and mst_fn <> gst_fn;

     mst_fn          gst_fn          fn_diff
     Dich-Hue        Hue-Dich        ?
     Fernanda-Maria  Maria-Fernanda  ?
     Marie-Louise    Louise-Marie    ?
     Fernanda-Maria  Maria-Fernanda  ?

Yet when I run an update using the same query I get a 2663: SUBSTR: string subscript out of bounds in TAB1.mst_fn. :

     UPDATE TAB1
     SET    fn_diff = '10'
     WHERE substr (mst_fn, 1, index (mst_fn, '-') -1) =
     substr (gst_fn,(index (gst_fn, '-')) +1)
     and substr (gst_fn, 1, index (gst_fn, '-') -1) =
     substr (mst_fn,(index (mst_fn, '-')) +1)
     and index (mst_fn, '-') > 0
     and index (gst_fn, '-') > 0
     and mst_fn <> gst_fn;

The column definations in the table are:

     mst_fn VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
     gst_fn VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

As always, any help would be gratefully received.

Thanks,

Martin

	<Prev	Next>		<<First	<Prev	Next>	Last>>