TomLiotta Dec 7, 2010   8:02 PM GMT

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:

Panpanky Dec 8, 2010   7:50 PM GMT

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:

philpl1jb Dec 8, 2010   11:48 PM GMT

Could an SQL index be created on the dec(myChar)? field? Phil

54,090 pointsBadges:

TomLiotta Dec 9, 2010   8:40 AM GMT

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:

philpl1jb Dec 9, 2010   10:49 AM GMT

Tom If you can create a view, can you also create an index? Phil

54,090 pointsBadges:

TomLiotta Dec 9, 2010   10:41 PM GMT

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:

philpl1jb Dec 9, 2010   11:33 PM GMT

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

54,090 pointsBadges:

TomLiotta Dec 10, 2010   6:27 AM GMT

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:

philpl1jb Dec 10, 2010   9:47 AM GMT

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

54,090 pointsBadges:

TomLiotta Dec 10, 2010   7:27 PM GMT

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:

philpl1jb Dec 11, 2010   1:24 PM GMT

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

54,090 pointsBadges:

Panpanky Dec 11, 2010   8:40 PM GMT

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: