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?

Answer Wiki

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.

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

Processing your response...

Discuss This Question: 12 &nbspReplies

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

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

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

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.

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.

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

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

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

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

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