DBA requires basic SQL assistance please (LONG POST)

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 10 Mar 2003 16:17:22 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70367894C@lnewton.leeds.lfs.co.uk>

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 - 10:17:22 CST