# how to convert the range of number to the date

i have a query

the 2 parameters are p_numnber_from and p_number_to

and the numbers are 1=JAN
2-FEB
3 - MAR

so if the parameters are from P_number_from =1
p_number_to= 7

how to convert it in the below select statement

SELECT distinct l.ledger_id,
h.period_name,
'2013',
'JAN',
'FEB',
1,
2,
default_effective_date,
m.segment2 legal_entity
FROM apps.gl_code_combinations m,
apps.gl_je_lines l
AND m.code_combination_id = l.code_combination_id
AND l.ledger_id = 2947
AND h.period_name in  ( p_number_from, p_number_to)
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You can convert the number to the month like the code below but I would suggest a CASE statement.

case p_number
when 1 then 'JAN'
when 1 then 'FEB'
...
end

From a number to a MON:
to_char(to_date(p_number,'MM'),'MON')
0
Author Commented:
no my question is

if p_number_from =1

and p_number_to = 5

then the query will be

AND h.period_name in  ('JAN-13', 'FEB-13', 'MAR-13', 'APR-13', 'MAY-13')

if p_number_from =9
and p_number_to=10

then

AND h.period_name in  ('SEP-13', 'OCT-13)

thats what i meant

the period_name string
0
Software DeveloperCommented:
You could write a function that converts the 2 input params or do a sub-select with listagg.
Are you familiar with those suggestions?!
0
Commented:
Off the top of my head, try this:

...
where h.period_name in (
from dual connect by level<=(p_number_to-p_number_from)+1
)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Software DeveloperCommented:
@slightwv: nice one *thumbs up*
0
Commented:
Thanks Alex.

I keep thinking there is an easier way...  I'm just not seeing it right now.
0
Author Commented:
one more question you mentioned

from dual connect by level<=(p_number_to-p_number_from)+1

but theere is one more parameter to it P_year

so if the year is 2013 then the above query id fine

if it is 2012

then

like can it be dynamic

i have a parameter p_year
0
Commented:
Again, I think I'm missing the 'easy' way but here is the one with p_year added:

...
...
0
Commented:
Simplified slightly:
``````...
select to_char(to_date(to_char((level+p_number_from-1),'00') || to_char(p_year),'MMYYYY'),'MON-YY')
from dual connect by level<=(p_number_to-p_number_from)+1
...
``````
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.