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?
 
SujithConnect With a Mentor Data 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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you pass a single string as parameter, and want it to be interpreted as "list", right?
0
 
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
 
awking00Commented:
Can you post your stored procedure? I assume you want it modified so it only returns records based on the parameter given.
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.