Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Generate values when given a range

633956Apr 14 2008 — edited Apr 14 2008
I have a table that provides me with a range of valid Dept Numbers.
Table: MXLISTDTL
Fields: SEQ_NUMBER (number), FR_MX_VAL (char), TH_MX_VAL (char)
Values:
1, 16312, 16313
2, 16410, 16419

I would like to generate a view with a complete list of possible values based on the ranges.
I was able to accomplish this, but the solution I came up with will require maintenance if a new range is added or if a range changes.

Here is what I have-
SELECT TO_CHAR((FR_MX_VAL - 1) + ROWNBR.R,'00000') VALID_AU
FROM LAWPROD.MXLISTDTL,
(SELECT ROWNUM R FROM ALL_OBJECTS WHERE ROWNUM <= 10 ) ROWNBR
WHERE SEQ_NUMBER = 2
UNION ALL
SELECT TO_CHAR((FR_MX_VAL - 1) + ROWNBR.R,'00000') VALID_AU
FROM LAWPROD.MXLISTDTL,
(SELECT ROWNUM R FROM ALL_OBJECTS WHERE ROWNUM <= 2 ) ROWNBR
WHERE SEQ_NUMBER = 1

The end result looks like this:
VALID_AU
16410
16411
16412
16413
16414
16415
16416
16417
16418
16419
16312
16313

You can see that if a range is added, I would have to add an additional UNION. In addition, if a range were changed, I would have to adjust the inner select to accommodate the number of possible values.

Can someone please help with a solution that would not require me to update the script as a result of additional ranges or changes to existing ranges?
Thank you,
elm
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2008
Added on Apr 14 2008
11 comments
500 views