Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   SUBSTR query
 
From:   Barrow, Martin

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016