Home » SQL & PL/SQL » SQL & PL/SQL » Query to extract information based on random search separated by commas
Query to extract information based on random search separated by commas [message #204311] Mon, 20 November 2006 02:01 Go to next message
nitinkoshymech
Messages: 2
Registered: November 2006
Location: Hyderabad,India
Junior Member
Hi,

I've a doubt.

I want to retrieve data from my tables based on random donor_codes that i supply separated by commas.I should be able to enter donorcodes in any order separated by commas like 1,10,50,200,300,5000,5500,6000,7000,etc

normally we write
select * from donor_tab where donor_code in (1,10,50,200,250,500);

But i want the text item to accept the commas and be able to enter donorcodes separated by commas so as to retrieve data for those donor codes i specify at a time from the donor_tab table

if i use IN clause,i will have to specify a limit and specify a variable for each donor_code i specify like
select * from donor_tab where donor_code in (p_donor_code1,p_donor_code2,p_donor_code3);
But here the problem is that i have to specify p_donor_code separately for each donor_code i specify and thats tedious .Can you help me?
Re: Query to extract information based on random search separated by commas [message #204399 is a reply to message #204311] Mon, 20 November 2006 08:04 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I can't quite get what you mean here:
Quote:
But i want the text item to accept the commas and be able to enter donorcodes separated by commas so as to retrieve data for those donor codes i specify at a time from the donor_tab table


What 'text item'? Can't you store the donorcodes in a collection (or array)?

[Updated on: Mon, 20 November 2006 08:05]

Report message to a moderator

Re: Query to extract information based on random search separated by commas [message #204404 is a reply to message #204399] Mon, 20 November 2006 08:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I must be overlooking something, because what is wrong with:

SQL> select t.employee_id
  2  ,      t.first_name
  3        ,t.last_name from employees t
  4  where t.employee_id in (&p);

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        112 Jose Manuel          Urman
        116 Shelli               Baida
        190 Timothy              Gates

[Updated on: Mon, 20 November 2006 08:49]

Report message to a moderator

Re: Query to extract information based on random search separated by commas [message #204407 is a reply to message #204311] Mon, 20 November 2006 09:38 Go to previous messageGo to next message
vshari
Messages: 9
Registered: October 2005
Location: India/UK
Junior Member
There a couple of ways you can do this if you are passing the list as a parameter to the procedure.

1. Use a collections parameter and then use it to
retrieve rows.
2. Pass it as a string (as you wish to send in this case) and
convert them to valid values using the instr() and substr()
and to_num() and then retrieve the records.
3. Or pass a string as this 'where xyz in (10,20,30)' and
append it to the sql statement and run it dynamically by
using execute immediate.



Please correct if i am wrong.

Thanks


Re: Query to extract information based on random search separated by commas [message #204441 is a reply to message #204311] Mon, 20 November 2006 15:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
Re: Query to extract information based on random search separated by commas [message #204470 is a reply to message #204404] Mon, 20 November 2006 22:34 Go to previous message
nitinkoshymech
Messages: 2
Registered: November 2006
Location: Hyderabad,India
Junior Member
Hi,

select * from donor_tab where donor_code in (&p);
works in sql but this is the query i'm giving in reports



I'm using Oracle 9i database. I already have data in my donor_tab.

I'm using the following query in my report to retrieve data

SELECT ALL DONOR_TAB.DONOR_CODE, INITCAP(TITLE_TAB.TITLE_NAME||' '||DONOR_TAB.FIRST_NAME||' '||DONOR_TAB.MIDDLE_NAME||' '||DONOR_TAB.LAST_NAME) donor_name,
RTRIM(INITCAP(RPAD(DONOR_TAB.ADDRESS1, 35, ' ')||RPAD(DONOR_TAB.ADDRESS2, 35, ' ')||RPAD(DONOR_TAB.STREET, 30, ' '))) DISPLAY_COL3,
INITCAP(CITY_MASTER_TAB.CITY_NAME||' - '||DONOR_TAB.PIN) DISPLAY_COL4,
INITCAP(CITY_MASTER_TAB.STATE_NAME||', '||CITY_MASTER_TAB.COUNTRY_NAME) DISPLAY_COL5,
UPPER('DONOR CODE'||' - '||DONOR_TAB.DONOR_CODE) DISPLAY_COL6
FROM DONOR_TAB, CITY_MASTER_TAB, TITLE_TAB
WHERE (DONOR_TAB.REMINDER IN :P_REMINDER
AND DONOR_TAB.LANG IN :P_LANG
AND DONOR_TAB.DONOR_CODE IN (_donor_code)---Here i want to write p_donorcode in such a way that allows me to input donorcodes separated by ',' upto any limit .I cannot put & as it isnt accepted as an SQL literal

AND ((DONOR_TAB.CITY_CODE = CITY_MASTER_TAB.CITY_CODE)
AND (DONOR_TAB.TITLE_CODE = TITLE_TAB.TITLE_CODE))
ORDER BY DONOR_TAB.DONOR_CODE

I'm using 6i forms and reports
Previous Topic: Access different schema from another schema.
Next Topic: Last 6 values
Goto Forum:
  


Current Time: Sun Dec 04 08:28:15 CST 2016

Total time taken to generate the page: 0.07033 seconds