Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    DB2 Substring command

    What is the DB2 equivelant of the SQL Server command Substring?
    ie Substring(column,1,3)

    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    same syntax

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Code:
    select d.direction_code
    , f.Direction_Id
    , SUBstring(vdate_Month_Id,5,2)
    , SUBstring(vdate_Month_Id,1,4)
    , max(vdate_Month_Id)-100
    , max(f.update_date)
    from db2admin.DW_JOC_ALL_DATA_TBL f, db2admin.REF_DIRECTION_LU_TBL d
    where f.direction_id = d.direction_id
    group by d.direction_code
    , f.Direction_Id;
    my error

    Code:
    [IBM][CLI Driver][DB2/NT] SQL0440N  
    No authorized routine named "SUBSTRING" of type "FUNCTION" 
    having compatible arguments was found.  SQLSTATE=42884
    What have I done wrong?
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Jan 2003
    Posts
    4,146
    The function name is SUBSTR.

    HTH

    Andy

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by ARWinner
    The function name is SUBSTR.

    HTH

    Andy
    new code
    Code:
    select d.direction_code
    , f.Direction_Id
    , SUBSTR(vdate_Month_Id,5,2)
    , SUBSTR(vdate_Month_Id,1,4)
    , max(vdate_Month_Id)-100
    , max(f.update_date)
    from db2admin.DW_JOC_ALL_DATA_TBL f, db2admin.REF_DIRECTION_LU_TBL d
    where f.direction_id = d.direction_id
    group by d.direction_code
    , f.Direction_Id;
    new error

    Code:
    [IBM][CLI Driver][DB2/NT] SQL0440N  No authorized 
    routine named "SUBSTR" of type "FUNCTION" 
    having compatible arguments was found.  SQLSTATE=42884
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    This is what the command center is telling me

    Code:
    This occurs in a reference to routine "<routine-name>", when the 
    database manager cannot find a routine it can use to implement 
    the reference. There are several reasons why this could occur: 
    
    o   "<routine-name>" was either incorrectly specified or does not 
        exist in the database.  
    
    o   A qualified reference was made, and the qualifier was     
        incorrectly specified.  
    
    o   The user's SQL path does not contain the schema to which the  
        desired function or method belongs, and an unqualified       
        reference was used.  
    
    o   The wrong number of arguments were included.  
    
    o   The right number of arguments were included in the function   
        or method reference, but the data types of one or more of the 
        arguments is incorrect.  
    
    o   The routine does not exist in the database with the same      
        function id that was used when the package was bound (applies 
        to static statements).  
    
    o   The mutator method corresponding to an attribute assignment   
        used in an UPDATE statement could not be found.  The data     
        type of the new value for the attribute is not a data type    
        that is the same or promotable to the data type of the   
        attribute.  
    
    o   The routine invoker is not authorized to execute the 
        routine.  
    
     
    
    User Response: 
    
    Fix the problem and retry.  This could involve catalog access, a 
    change to the statement, a grant of the execute privilege to the 
    routine invoker, the addition of new functions, and/or a change 
    to the SQL path.  
    
     sqlcode :  -440 
    
     sqlstate :  42884
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  7. #7
    Join Date
    Jan 2003
    Posts
    4,146
    Jim,
    What data type is vdate_Month_Id?

    Andy

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    integer
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  9. #9
    Join Date
    Jan 2003
    Posts
    4,146
    Jim,
    There is your problem. You need to convert it to a string before you can get substrings from it. Use either substr(char(vdate_Month_Id),x,y) or substr(digits(vdate_Month_Id),x,y), whichever you need.

    Andy

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by ARWinner
    Jim,
    There is your problem. You need to convert it to a string before you can get substrings from it. Use either substr(char(vdate_Month_Id),x,y) or substr(digits(vdate_Month_Id),x,y), whichever you need.

    Andy
    Awsome.

    Thanks for the help
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    Always search the SQL Reference for SQL Related problems ...

    If you are migrating from a different RDBMS to DB2 (or your experience is with another RDBMS), visit www.ibm.com/db2/migration ... There are quiet a few documents which compare DB2 with others

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •