Using Number range on an AS/400 file with Char type field index

25 pts.
Tags:
AS/400 database
AS/400 performance
Hi, I have scenario to filter records for screen presentation based on number range provided on screen.In database the numbers are stored as char with no padding.How can I use this character index to get the results without having to read entire file?

Software/Hardware used:
AS/400

Answer Wiki

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

If the value of the number is stored right justified in the character field, you can create a LF to get your records. You would just setup your number range as character fields right justified suppressing the leading zeros..
This would be the quickest.
Next might be to use SQL but before we get into that, it would be best if we knew other things like the number of records in the file.

Discuss This Question: 12  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
  • TomLiotta
    In database the numbers are stored as char with no padding. What does "no padding" mean? It would normally mean that the value filled the entire field; therefore all leading zeros would be in place. Just use a zoned-decimal key field in your program. There shouldn't be any problem. ...unless "no padding" really means "padded on the left with blanks" which is an incredibly problematic setup for a key field that should be fixed. Tom
    125,585 pointsBadges:
    report
  • Panpanky
    Thanks for jumping on this. In database the numbers are stored as char with no padding - The 15 char fiels in database is filled with numbers(stored as char- left justified) For eg.- 1000053 1000054 167018 167018 and so on.......The number of records in the range on 2 to 3 million. The screen built on top of this file has user option to enter the range of numbers..... From what I understand if numbers were stored as 000000001000053, 000000001000054 000000000167018 000000000167018 Using a setll on the indexed file from start num(from screen) till end range num (from screen) would work perfect. However due to incorrect way of number stored as char I have to read entire file to represent the entered range.I was thinking if there is smart way of doing this. Hope this clarifies my question. Thanks for the attention.
    25 pointsBadges:
    report
  • philpl1jb
    Could an SQL index be created on the dec(myChar)? field? Phil
    50,205 pointsBadges:
    report
  • TomLiotta
    1000054
    167018
    The first value has seven characters, and the second one has only six characters. Unless this is a variable length field, the second value is padded with a blank on the right. But you can create a SQL VIEW over the columns to interpret the character values as numeric:
    dec(trim(charval),10,0) as NumVal 
    That converts the 'charval' column to a 'NumVal' column for presentation. You can specify an ORDER By to read the view. With multiple columns, though, and with a few million records, it's going to cost a lot of processing time unless this is a one-time run or it's very infrequent. Personally, I would probably push forcefully to correct the data. If the user wants it ordered numerically, then the data should be numeric.
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom If you can create a view, can you also create an index? Phil
    50,205 pointsBadges:
    report
  • TomLiotta
    If you can create a view, can you also create an index? In V6R1, it's documented as possible. You can specify an expression in place of a column name. The list of excluded builtin functions does not include DEC() nor TRIM(), so I'd expect it to work. Before 6.1, you must reference a column name from the base table (according to the SQL Reference). Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    So with an index over this "field" then you could use a Select statement and a fetch loop to work with the records that you want and expect good performance. Phil
    50,205 pointsBadges:
    report
  • TomLiotta
    So with an index over this “field” then you could use a Select statement and a fetch loop... I can imagine an index, but I'm not clear how to use it. The CURSOR SELECT would access either the base table or the view. The ORDER BY could specify the derived column of the view, but that might not be seen as matching the index. I'd like to think that the optimizer is smart enough. Or it could specify an expression that matched the index. That might mean that the base table would need to be in the SELECT, but then the view wouldn't be useful. Or I suppose that the view could present both the base column and the derived column value -- except then the view wouldn't be needed at all because the SELECT could have the same items in its column list. Maybe not a bad thought at all. It'd be worth it just to see how it needed to be done. I'd probably still put more effort into correcting the troublesome database design so that none of this would even be needed. But it's interesting looking at how to make it work. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Tom: Cleaning up the data would be optiomal. But, if that's impossible, I would use a view of the physical including this "Field" definition or a select in RPG including the "Field" definition using the physical file or the view. , In either case the "Field" is used in the select , where, and order by clauses. with the index matching the order by rules with this "Field" first .. then I would hope the optimizer would choose the indexed path. Phil
    50,205 pointsBadges:
    report
  • TomLiotta
    I would use a view of... I'd try the same and it should work -- at least at i 6.1 and later, which might not be feasible for this question. Visual Explain should tell all quickly. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Summery in v6.1 SQL indexes can include derived columns. These Indexes probably will be used by the SQL optimizer when the order by list is the same as the index. This opens up a wide range of options for solving complex problems with good performance. Please let us know when you find exciting ways to use this feature. Phil
    50,205 pointsBadges:
    report
  • Panpanky
    I have opted for data storage cleanup....In meantime user void the range function on screen and go with specific number. Thanks you all....
    25 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