If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 Substring command

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
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
Reply With Quote
  #2 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
same syntax
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
The function name is SUBSTR.

HTH

Andy
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
Jim,
What data type is vdate_Month_Id?

Andy
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
integer
Jim
__________________
------------
And back to SQL Server....I always find my way home
View my Linkedin profile
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Aug 2002
Location: Charlotte NC
Posts: 663
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
Reply With Quote
  #11 (permalink)  
Old
Super Moderator
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On