Home » SQL & PL/SQL » SQL & PL/SQL » SQL IN FUNCTION (ORACLE 10G)
SQL IN FUNCTION [message #599680] |
Mon, 28 October 2013 02:29 |
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
|
|
|
|
Re: SQL IN FUNCTION [message #599684 is a reply to message #599680] |
Mon, 28 October 2013 03:02 |
|
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>
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 17:00:14 CDT 2024
|