How to query with multiple values for one field ?

I have a table with following fields that I am interested in :
PROG_NAME           VARCHAR(200)
PROG_Start_Month  VARCHAR(2)
PROG_End_Month.   VARCHAR(2)

I have a Stored Proc which queries this table and returns the records.
I now have  to add parameter - p_MONTH to the existing Stored Proc. This paramter will be a string and will contain month values which could be '00' to '12' and can be multiple values separated with commas.
Example '00'   or   '01'    or    '02'    or    '02,06,09'.

I want to get the records where my Program is running for any month provided in the parameter.
Example: If the parameter p_MONTH contains '00', I should get all programs
If parameter contains '02' I should get all programs which either starts in or before mont '02' OR end in or after month '02'. Bascially all programs valid during month '02'
.
Similarly if the parameter contains '02, 06, 09' then I should get all programs that are valid for any of these three month in the parameter. That is all programs where
PROG_START_MONTH <= '02' OR PROG_END_MONTH >='02'
PLUS
PROG_START_MONTH <= '06' OR PROG_END_MONTH >='06'
PLUS
PROG_START_MONTH <= '06' OR PROG_END_MONTH >='09'

There has to be a way to doing this in the WHERE CLAUSE (some smart way I guess) other than splitting the parameter and doing something else which I am drawing blank.
nazzie303Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
WHERE ( PROG_START_MONTH <= '02' AND PROG_END_MONTH >='02' )
  OR  ( PROG_START_MONTH <= '06' AND PROG_END_MONTH >='06' )
  OR  ( PROG_START_MONTH <= '06' AND PROG_END_MONTH >='09' )

Open in new window

0
nazzie303Author Commented:
I guess you are assuming that I will have 02, 06, and 09 always.  
That was just an example I provided,  The values inside the paramtershall be a string and  could be combination of any months in the year. Example
1. '01,,02,,06,,09'
2. '02, 05, 08'
3. '09, 04'  ......and so on.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you pass a single string as parameter, and want it to be interpreted as "list", right?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SujithData ArchitectCommented:
provide some sample data.
0
nazzie303Author Commented:
angel: You are right. I want the months(Numbers) inside the string to be interpreted individually so that I can compare then with the Start and End Month.

sujith: here is data example
PROG_NAME              PROG_Start_Month     PROG_End_Month
A                                  01                              03
B                                  03                              05
C                                  02                              04
D                                  01                              02
So basically If I was to pass ('03,09') I should be able to get Prog A, B, C since these programs either start on Month 03, or end on MOnth 03 or contains month 03. But NOT D, since it is from 01 t0 02 only.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check out the function  split  on this page:
http://articles.techrepublic.com.com/5100-10878_11-5259821.html

using that, you can JOIN

SELECT t.*
  FROM yourtable t
  JOIN Split('01,02,06,09') l
    ON ( t.PROG_START_MONTH <= l.Value AND t.PROG_END_MONTH >= l.Value )
    OR ( l.Value = '00' )

Open in new window

0
awking00Information Technology SpecialistCommented:
Can you post your stored procedure? I assume you want it modified so it only returns records based on the parameter given.
0
SujithData ArchitectCommented:
Use the following query.
SQL> select * from tbl1;
 
PROG_NAME            PROG_START_MONTH     PROG_END_MONTH
-------------------- -------------------- --------------------
A                    01                   03
B                    03                   05
C                    02                   04
D                    01                   02
 
4 rows selected.
 
SQL> select A.* 
  2  from tbl1 A,
  3  (
  4  select to_number(regexp_substr(str, '([^,]+)', 1 , lvl)) mth
  5  from 
  6  ( select str, level lvl 
  7    from ( select '03,09' str from dual) 
  8    connect by level <= length(str) -  length(replace(str, ',')) + 1) Y
  9  ) Z
 10  where z.mth = '00' OR to_number(prog_start_month) <= Z.mth and to_number(prog_end_month) >= z.mth;
 
PROG_NAME            PROG_START_MONTH     PROG_END_MONTH
-------------------- -------------------- --------------------
A                    01                   03
B                    03                   05
C                    02                   04
 
3 rows selected.

Open in new window

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
nazzie303Author Commented:
Thanks. I used this to built it a little differently. But it wa a great help
0
SujithData ArchitectCommented:
The solution I have provided solves exactly the question you have asked.

A grade of "B" is not appreciated.
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.