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,
               h.je_header_id,
               m.segment2 legal_entity
          FROM apps.gl_code_combinations m,
               apps.gl_je_headers h,
               apps.gl_je_lines l
         WHERE     h.je_header_id = l.je_header_id
               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)
pardeshirahulAsked:
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.

slightwv (䄆 Netminder) 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
pardeshirahulAuthor 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
Alexander Eßer [Alex140181]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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Off the top of my head, try this:

...
where h.period_name in (
   select to_char(add_months(trunc(sysdate,'YYYY'),(p_number_from-1)+level-1),'MON-YY')
   from dual connect by level<=(p_number_to-p_number_from)+1
)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
@slightwv: nice one *thumbs up*
0
slightwv (䄆 Netminder) Commented:
Thanks Alex.

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

 select to_char(add_months(trunc(sysdate,'YYYY'),(p_number_from-1)+level-1),'MON-YY')
   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
slightwv (䄆 Netminder) Commented:
Again, I think I'm missing the 'easy' way but here is the one with p_year added:

...
select to_char(add_months(to_date('01' || to_char(p_year),'MMYYYY'),(p_number_from-1)+(level-1)),'MON-YY')
...
0
slightwv (䄆 Netminder) 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
...

Open in new window

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.