Home » SQL & PL/SQL » SQL & PL/SQL » SQL IN FUNCTION (ORACLE 10G)
SQL IN FUNCTION [message #599680] Mon, 28 October 2013 02:29 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

Dear Experts

I have a requirement to use Oracle SQL IN function as a variable use in query to get result i make lot of variations to get result but it doesnt work please help me out of this problem.

I have a table data like
msg_id	msg	          select_loc	          select_days
11	HELLO	          'ALL'	                  '21','22','23','24','25','26','27'
13	MESSAGE TEST	  '410','210','001'	  '13'
12	TESTING 2	  '210'	                  '28','29','30','31','1','2','3'
19	TESTING MSG 99	  '120'	                  'ALL'
20	TESTING 777	  'ALL'	                  'ALL'



now i would like to make query with these 2 in-list items SELECT_LOC and SELECT_DAYS

suppose if i write sql like
select * from table where 
(select_loc='ALL' or :p_loc in select_loc) 
and 
(select_days='ALL' or to_char(sysdate,'dd) in select_days);


where if :p_loc=410 and sysdate='27-oct-2013', so i have to get 2 records like

11	HELLO	          'ALL'	                  '21','22','23','24','25','26','27'
20	TESTING 777	  'ALL'	                  'ALL'



where if :p_loc=210 and sysdate='28-oct-2013', so i have to get 2 records like
12	TESTING 2	  '210'	                  '28','29','30','31','1','2','3'
20	TESTING 777	  'ALL'	                  'ALL'


I hope i make it easy to understand what my requirement is ? well can i handle it in a query or i have to write something pl/sql function or procedure?
do suggest me


Regards

Anwer
icon3.gif  Re: SQL IN FUNCTION [message #599681 is a reply to message #599680] Mon, 28 October 2013 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hint for conditions like ":p_loc in select_loc":
','||select_loc||',' like '%,'||:p_loc||',%'


Re: SQL IN FUNCTION [message #599684 is a reply to message #599680] Mon, 28 October 2013 03:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option:
SQL> select *
  2  from test
  3  where (   instr(select_loc, &p_loc) > 1
  4         or select_loc = '''ALL'''
  5        )
  6        and
  7        (   instr(select_days, to_char(&p_sysdate, 'dd')) > 1
  8         or select_Days = '''ALL'''
  9        );
Enter value for p_loc: 410
Enter value for p_sysdate: date '2013-10-27'

    MSG_ID MSG            SELECT_LOC        SELECT_DAYS
---------- -------------- ----------------- ----------------------------------
        11 HELLO          'ALL'             '21','22','23','24','25','26','27'
        20 TESTING 777    'ALL'             'ALL'

SQL> /
Enter value for p_loc: 210
Enter value for p_sysdate: date '2013-10-28'

    MSG_ID MSG            SELECT_LOC        SELECT_DAYS
---------- -------------- ----------------- ----------------------------------
        12 TESTING 2      '210'             '28','29','30','31','1','2','3'
        20 TESTING 777    'ALL'             'ALL'

SQL>
Re: SQL IN FUNCTION [message #599712 is a reply to message #599684] Mon, 28 October 2013 06:00 Go to previous message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

THANK YOU ALL, IT WORKS



Previous Topic: How to get department wise effective rate
Next Topic: ad hoc MINUS - compare values in SQL code to values in table
Goto Forum:
  


Current Time: Thu Mar 28 17:00:14 CDT 2024