Home > IT Answers > AS/400 > Best way to retrieve values, SQL or Queries and how?
BUG68
225 pts.
 Best way to retrieve values, SQL or Queries and how?
CLP, Query/400, RPG IV, SQL, V5R4, V5R4M0
Hello everybody,

my problem is, in a iseries i have ia file and in this file exist one field with 10000 lenght (character) and i want retrieve a value like (  60F: ) but i don´t know in what position, after i find this value i need the follow values.

ex: this is a part of one record

 {4:  :20:EXT/021009/00007  :28C:00140/001  :60F:C021007EUR311792,64  :61:0210071008D350,NMSC66182

i hope anyone help me, give me an idea or example how i visualize this values.

thanks in advance.



Software/Hardware used:
AS400 V5R4M0 , ISERIES,
ASKED: October 1, 2009  10:33 AM
UPDATED: November 3, 2009  1:11 PM
RELATED QUESTIONS
Answer Wiki:
Bug68 In SQL the locate command gives the position of string1 in string2. So since locate returns the starting position of the string, it can be used in the substring function. Select substring(MyField, locate('60F',MyField,1),80) from MyFile where locate('60F',MyField,1),80) > 0 Here it displays 80 characters starting with the '60F' Phil ----------- :60F and the select had an error..in the where clause Select substring(MyField, locate(' :60F',MyField,1),80) from MyFile where locate(' :60F',MyField,1) > 0 Phil
Last Wiki Answer Submitted:  October 12, 2009  9:17 pm  by  philpl1jb   44,070 pts.
All Answer Wiki Contributors:  philpl1jb   44,070 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

To find it in RPG, use the %scan to scan for the string. It returns the position the string is found.

graybeard52
 3,115 pts.
 

Is there always trailing blanks and a colon after the value? Or is the leading colon for the next segment the only delimiter?

Tom

TomLiotta
 107,735 pts.
 

HI,

about your question, Tom, yes always trailing blanks and a colon after the value.

thanks

BUG68
 225 pts.
 

Then I would say you should scan for ‘ :60F:’
The more data you can include in the scan, the better chance you have of not changing something incorrectly.
Now once you fine the starting position in the string, you can rplace the value.

CharlieBrowne
 32,785 pts.
  
SELECT
   sourceColumn ,
   posstr(sourceColumn,'60F:')+length('60F:') as locS,
   locate(' :',sourceColumn, posstr(sourceColumn,'60F:') ) as locE,
   substr(sourceColumn, posstr(sourceColumn,'60F:')+length('60F:'),locate(' :',sourceColumn, posstr(sourceColumn,'60F:') ) -length('60F:') -1) as extractSubstring
 FROM sourceTable

I used sourceColumn as the name of the column you’re extracting from and sourceTable as the name of the table. The first three columns in the select-list are there just for visual comparison.

posstr(sourceColumn,’60F:’) is where the first search-string starts
locS is the starting position of your extract-string in sourceColumn
locE is the position of the ending search-string in the substring beginning where the first search-string starts

extractSubstring is all you should need. Where I used [length('60F:')], you can probably just use the constant length of ’60F:’ which is (4).

Regardless, I probably wouldn’t do it this way. If it needed to be done in SQL, I’d create a UDF() and code the logic. Along with the logic, I’d explain what in the world I was doing.

With a UDF(), you could also pass in any search values; you wouldn’t be limited to ’60F:’ nor ‘ :’. A UDF() could test to ensure that ’60F:’ appeared in the source string and that ‘ :’ was in a position after the first search-string.

Trying to make this work in a single SQL statement is asking for trouble when the first minor change in specs comes along. I’m not sure I even like trying to explain here what the above statement does! Very ugly, eh?

Tom

TomLiotta
 107,735 pts.
 

thanks Tom for this, i will try test and then send my comments.

bug68

BUG68
 225 pts.
 

In query400 use the Like keyword.

RonKoontz
 1,780 pts.
 

Hi, again

thanks Tom for you tip, this select instruction are very usefull, and for my needs work well.
Normally i use the suggestion of CharlieBrowne inside of programs but in that case i need this for quit solution.

thank everybody.

BUG68
 225 pts.