Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to query with multiple values for one field ?

Posted on 2008-11-18
10
Medium Priority
?
483 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:nazzie303
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22988151
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
 

Author Comment

by:nazzie303
ID: 22988636
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22988719
so, you pass a single string as parameter, and want it to be interpreted as "list", right?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 27

Expert Comment

by:sujith80
ID: 22990918
provide some sample data.
0
 

Author Comment

by:nazzie303
ID: 22993850
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22995290
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
 
LVL 32

Expert Comment

by:awking00
ID: 22995480
Can you post your stored procedure? I assume you want it modified so it only returns records based on the parameter given.
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1200 total points
ID: 23000790
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
 

Author Closing Comment

by:nazzie303
ID: 31517982
Thanks. I used this to built it a little differently. But it wa a great help
0
 
LVL 27

Expert Comment

by:sujith80
ID: 23087456
The solution I have provided solves exactly the question you have asked.

A grade of "B" is not appreciated.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month21 days, 3 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question