How to query with multiple values for one field ?
Posted on 2008-11-18
I have a table with following fields that I am interested in :
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'
PROG_START_MONTH <= '06' OR PROG_END_MONTH >='06'
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.