Re: DBA requires basic SQL assistance please (LONG POST)

I quick look seems to indicate that you are not returning anything other than the integer for MyCount--- Where does the SP get the character string to search with the IN operator ?

Also, you are trying to use :MySoundex but no such bind variable exists until a call to BuildSoundex ( once the OUT value is set)

...

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote:

>Afternon all,
>
>=====================
>Oracle 8174 EE 64 bit.
>HPUX 11.00 64 bit.
>=====================
>
>I'm certain I've done this before, but try as I might, I cannot seems to
>make it work now ! What I think I've done before is this :
>
>select * from table where something in (some_function());
>
>but, the bit in parenthesis is generated as output from a function
>either via an OUT parameter or by a direct function result. So the
>function returns <'a','b','c'> and the in clause 'just worked'. Or at
>least, that's what I think happened.
>
>Here's where I'm losing the plot :
>
>I have a table in the database holding employee names as a single
>column, in the format first_name || space || last_name. I have soundexed
>every word (separated by space or hyphen) in the name column into a
>separate table which holds a soundex code and the employee id. So my own
>name 'Norman Dunbar' has two rows in the soundex table - 'N655' and
>'D516', both of which point back at my row in the employee table.
>
>When I search for an employee by name or part therof, I can find my row
>using soundex('Norman') or soundex('Dunbar') - which is the intention I
>have for the app. However, my problem is based on the waffle at the top
>of this post - I cannot get the 'select ... from ... where ... in()' to
>work.
>
>I have a function BuildSoundex which returns the count of the number of
>soundex codes generated for a passed in varchar2 name, and an output
>paramater which returns the list formatted with quotes and commas as
>appropriate :
>
>CREATE OR REPLACE FUNCTION Buildsoundex(iString IN VARCHAR2,
> oSoundex OUT VARCHAR2,
> iWithCommas IN BOOLEAN DEFAULT
>FALSE,
> iWithQuotes IN BOOLEAN DEFAULT
>FALSE)
>RETURN INTEGER
>AS
> --
>======================================================================
> -- This function builds a string of 4 character soundex codes in
>oSoundex
> -- and returns the number of them as the function result. The
>output can
> -- optionally have quotes around each soundex code or commas
>between each
> -- but if you ask for quotes, you get commas !
> --
>======================================================================
> MyString VARCHAR2(500); -- Temporary working copy of
>iString
> MySoundex VARCHAR2(500); -- Working area to build soundex
>codes
> ThisSoundex VARCHAR2(6); -- A single soundex code - could
>be NULL !
> MySpace INTEGER; -- Where is the next space in
>MyString ?
> MyCount INTEGER := 0; -- How many words have been
>soundexed ?
> MyStart INTEGER := 1; -- Start of next word in
>MyString
> MyComma BOOLEAN := iWithCommas; -- Do we wwant commas between
>soundex codes ?
>BEGIN
> -- if nothing to do, get out of Dodge quickly !
> IF iString IS NULL THEN
> oSoundex := iString;
> RETURN 0;
> END IF;
>
> -- If user requested quotes then s/he gets commas as well !
> IF iWithQuotes THEN
> MyComma := TRUE;
> END IF;
>
> -- Build the soundex string. Beware that some soundexes can be NULL
> -- so test for those and don't count them.
> MyString := trim(BOTH ' ' FROM iString) || ' ';
> MySpace := INSTR(MyString, ' ');
> WHILE MySpace > 0 LOOP
> ThisSoundex := SOUNDEX(SUBSTR(MyString, MyStart, MySpace -1));
> IF ThisSoundex IS NOT NULL THEN
> MyCount := MyCount + 1;
>
> -- Do we need quotes ?
> IF iWithQuotes THEN
> ThisSoundex := '''' || ThisSoundex || '''';
> END IF;
>
> MySoundex := MySoundex || ThisSoundex;
> IF MyComma THEN
> MySoundex := MySoundex || ',';
> END IF;
> END IF;
> MyStart := MySpace + 1;
> MySpace := INSTR(MyString, ' ', MyStart);
> END LOOP;
>
> -- If we are adding commas, then we have one at the end and we
>don't want it
> IF iWithCommas THEN
> MySoundex := SUBSTR(MySoundex, 1, LENGTH(MySoundex) -1 );
> END IF;
>
> oSoundex := MySoundex;
> RETURN MyCount;
>END;
>
>
>This works fine. I get the count back and the list of soundexes to use
>as follows :
>
>DECLARE
> MySoundex VARCHAR2(20); -- Because I know I won't generate a bigger
>than 20 chare return.
> MyCount INTEGER := 0;
>BEGIN
> MyCount := BuildSoundex('Norman Dunbar', MySoundex, TRUE, TRUE);
> DBMS_OUTPUT.PUT_LINE('MyCount := ' || TO_CHAR(MyCount));
> DBMS_OUTPUT.PUT_LINE('MySoundex := ' || MySoundex);
>END;
>
>The dbms_output buffer looks like the following :
>
>MyCount := 2
>MySoundex := 'N655','D516'
>
>
>Now, what I can do this :
>
>SELECT Employee_name
>FROM employee
>WHERE employee_id IN (SELECT esx_employee_id
> FROM employee_soundex
> WHERE esx_soundex IN ('N655','D516')
> );
>
>Which works fine with the soundex codes hard coded in, but I really
>really want to do this instead :
>
>BEGIN
> MyCount := BuildSoundex('Norman Dunbar', MySoundex, TRUE, TRUE);
> FOR e IN (SELECT Employee_name
> FROM employee
> WHERE employee_id IN (SELECT esx_employee_id
> FROM employee_soundex
> WHERE esx_soundex IN (:MySoundex)
> )
> )
> LOOP
> DBMS_OUTPUT.PUT_LINE(e.employee_name);
> END LOOP;
>END;
>
>As I said, I'm almost 100% certain I've done this in the past, using a
>function to generate an 'in' list and having a select return all the
>rows in that list. I'm going to be building this into an app (in PL/SQL)
>and I'd rather not be building dynamic sql all the time, but if that's
>the only way, then it will have to do.
>
>Hopefully the wrapping, fonts etc won't get screwed up too much.
>
>Cheers for any comments, put downs, advice, etc - be brutal, I can take
>it :o)
>
>Regards,
>Norman.
>
>-------------------------------------
>Norman Dunbar
>Database/Unix administrator
>Lynx Financial Systems Ltd.
>mailto:Norman.Dunbar_at_LFS.co.uk
>Tel: 0113 289 6265
>Fax: 0113 289 3146
>URL: http://www.Lynx-FS.com
>-------------------------------------
Received on Mon Mar 10 2003 - 12:37:06 CST