Error while using STRSQL
When I run the following command I get an error while using STRSQL. Is this a valid statement or am I losing my mind.
select right(gmans, 5) from f0901
(This is the error I get)
RIGHT in *LIBL type *N not found.
If I use the "Left" command it works fine.
Software/Hardware used:
Software/Hardware used:
ASKED:
September 15, 2005 3:53 PM
UPDATED:
June 14, 2013 3:17 PM
Answer Wiki:
There is no RIGHT function in SQL only a LEFT. Your best bet is to use a SUBSTR function if the field is an exact length to extract the last 6 positions
Example - Select SUBSTR(gmans,strpos,6)from f0901
where strpos is a numeric starting position.
=======================================================
Note that RIGHT() is a scalar function available in DB2 on iSeries as of V5R3.
Tom
========================================================
If you are at a OS Version that does NOT support RIGHT() then you can emulate it with the following:
<pre> select substr(trim(gmans), max(length(trim(gmans)) - 5, 1), 6) from f0901 </pre>
BigKat
All Answer Wiki Contributors:
Michael Tidmarsh 
14,060 pts. ,
BigKat 7,205 pts. ,
TomLiotta 110,135 pts. ,
WaltZ400 645 pts.
14,060 pts. ,
BigKat 7,205 pts. ,
TomLiotta 110,135 pts. ,
WaltZ400 645 pts. To see all answers submitted to the Answer Wiki: View Answer History.
Losing your mind
According to the “DB2 Universal Database for iSeries SQL Reference” LEFT is a Scalar Function, listed under Built-in Functions.
There is no definition for RIGHT. So presumably RIGHT does not exist (as the error message suggests)
Don’t presume, read the docs.
this doc is available in PDF format in the ibm doc cdrom : …InfoCenterinfodb2rbafzmst.pdf
or on line
Personnaly, I have a local copy of this 2-cd-intranet-site on my PC, it is a must !