Categorizing Query

[VBAHole22]

I have a field with a text value. I want to write a function that updates another field with the value of the first letter of that text field, but only when it falls within a specific group. For example, if the text field is 'goat' then the first letter if 'g' and I have a table that lists acceptable values and 'g' is in the table so update the field with 'g' if 'g' were not in the table then update it with 'z' a catch-all value.

Here is what I have so far

CREATE OR REPLACE PROCEDURE Update_FileType
AS
BEGIN

UPDATE STATS
SET FILETYPE = UPPER(SUBSTR(KTEXT,1,1))
;
END;
/

So FILETYPE is the field I want to update and the value to assess is in the field KTEXT. This function is obviously deficient. All it does it update the field to the first letter. I need it to check against my ACCEPT table to see if the single character is in there or not. Alternatly I could list those values here instead of having them in a table (there are only 12).

My problem is that I am trying to do this without looping through each record (because I have 500,000 recs). But when you use an IF statement do you have to loop?

Here is my psuedo code that doesnt' work
CREATE OR REPLACE PROCEDURE Update_FileType1

AS
type refCur is ref cursor;
DGNtemp VARCHAR2(1);
l_cursor refCur;
l_rec dual%rowtype;


BEGIN

FOR DGNTemp IN (SELECT KTEXT FROM STATS)
LOOP

open l_cursor for SELECT * FROM ACCEPTABLE_FILETYPES WHERE DGNTemp IN (SELECT FILETYPE FROM ACCEPTABLE_FILETYPES);

fetch l_cursor into l_rec;

IF ( l_cursor%notfound ) THEN

UPDATE STATS
SET FILETYPE = 'Z';

ELSE
UPDATE STATS
SET FILETYPE = UPPER(SUBSTR(KTEXT,1,1));

END IF;
END LOOP;
END;
/


Any suggestions?

 

[rac2]

Maybe use a case expression

UPDATE Stats SET
    filetype = CASE
                 WHEN UPPER(SUBSTR(KText,1,1)) IN ('g',...,'c') THEN UPPER(SUBSTR(KText,1,1))
                 ELSE 'z'
               END

or maybe

UPDATE Stats SET
    filetype = CASE
                 WHEN EXISTS( SELECT * FROM tblAcceptable WHERE UPPER(SUBSTR(KText,1,1)) = good_values ) THEN UPPER(SUBSTR(KText,1,1))
                 ELSE 'z'
               END

Yes an IF statement is not part of SQL, but CASE is. Use IF in procedures, CASE in SQL expressions.