Best way to retrieve values, SQL or Queries and how?

225 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • graybeard52
    To find it in RPG, use the %scan to scan for the string. It returns the position the string is found.
    3,115 pointsBadges:
    report
  • TomLiotta
    Is there always trailing blanks and a colon after the value? Or is the leading colon for the next segment the only delimiter? Tom
    125,585 pointsBadges:
    report
  • BUG68
    HI, about your question, Tom, yes always trailing blanks and a colon after the value. thanks
    225 pointsBadges:
    report
  • CharlieBrowne
    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.
    39,815 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • BUG68
    thanks Tom for this, i will try test and then send my comments. bug68
    225 pointsBadges:
    report
  • RonKoontz
    In query400 use the Like keyword.
    1,780 pointsBadges:
    report
  • BUG68
    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.
    225 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following