Generate values when given a range
633956Apr 14 2008 — edited Apr 14 2008I 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