Query to extract information based on random search separated by commas
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?
# 2 Re: Query to extract information based on random search separated by commas
Hi,
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 (:p_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
