Hi all,
Thank you for all who responded to my range checking
question. I created a function check_range with in
parameter varchar2, and return varchar2.
This works fine:
select check_range('301') from FRANCHISE_AREA;
But when I select the column, I got:
select check_range(FRANCHISE_NAME) from FRANCHISE_AREA
*
ERROR at line 1:
ORA-20599: ORA-06502: PL/SQL: numeric or value error:
character to number
conversion error
ORA-06512: at "ICSS.CHECK_RANGE", line 69
ORA-06512: at line 1
The table structure shows FRANCHISE_NAME is varchar2:
SQL> desc FRANCHISE_AREA;
Name Null?
Type
----------------------------------------- --------
----------------------------
FRANCHISE_ID NOT NULL
NUMBER(10)
FRANCHISE_NAME NOT NULL
VARCHAR2(32)
FRANCHISE_AUTHORITY NOT NULL
VARCHAR2(100)
FRANCHISE_CONTACT_ADDRESS_ID NOT NULL
NUMBER(10)
FRANCHISE_CONTACT_PHONE_ID
NUMBER(10)
FRANCHISE_CONTACT_NAME
VARCHAR2(32)
FRANCHISE_CUI_NUMBER NOT NULL
VARCHAR2(10)
FRANCHISE_EFFECTIVE_DATE NOT NULL
DATE
FRANCHISE_EXPIRATION_DATE
DATE
REMITTANCE_SERVICE_CENTER_ID NOT NULL
NUMBER(5)
BILLING_SERVICE_CENTER_ID NOT NULL
NUMBER(5)
RETURN_ADDR_SERVICE_CENTER_ID NOT NULL
NUMBER(5)
CUI_SERVICE_CENTER_ID
NUMBER(5)
COMPANY_ID NOT NULL
NUMBER(10)
The code for function is:
CREATE OR REPLACE FUNCTION check_range(
franchise_name varchar2)
return
varchar2
as
ifranchise_name number;
franchise_code varchar2(2);
StoO_error number;
StoO_errmsg VARCHAR2(255);
begin
ifranchise_name := to_number(franchise_name);
if ifranchise_name >= 301 and ifranchise_name
<= 390 then
franchise_code := 'QD';
elsif ifranchise_name >= 391 and
ifranchise_name <= 392 then
franchise_code := 'NW';
elsif ifranchise_name >= 393 then
franchise_code := 'SA';
elsif ifranchise_name >= 394 then
franchise_code := 'NT';
elsif ifranchise_name >= 415 and
ifranchise_name <= 420 then
franchise_code := 'NW';
elsif ifranchise_name >= 421 then
franchise_code := 'NT';
elsif ifranchise_name >= 422 and
ifranchise_name <= 434 then
franchise_code := 'NW';
elsif ifranchise_name >= 435 and
ifranchise_name <= 437 then
franchise_code := 'SA';
elsif ifranchise_name >= 439 and
ifranchise_name <= 455 then
franchise_code := 'NW';
elsif ifranchise_name >= 456 then
franchise_code := 'SA';
elsif ifranchise_name >= 457 then
franchise_code := 'NW';
elsif ifranchise_name >= 459 then
franchise_code := 'SA';
elsif ifranchise_name >= 460 then
franchise_code := 'NW';
elsif ifranchise_name >= 465 and
ifranchise_name <= 467 then
franchise_code := 'SA';
elsif ifranchise_name >= 473 then
franchise_code := 'NT';
elsif ifranchise_name >= 475 and
ifranchise_name <= 476 then
franchise_code := 'SA';
elsif ifranchise_name >= 477 and
ifranchise_name <= 479 then
franchise_code := 'NW';
elsif ifranchise_name >= 483 then
franchise_code := 'SA';
elsif ifranchise_name >= 487 and
ifranchise_name <= 498 then
franchise_code := 'NW';
elsif ifranchise_name >= 505 and
ifranchise_name <= 510 then
franchise_code := 'VC';
elsif ifranchise_name >= 511 and
ifranchise_name <= 515 then
franchise_code := 'TA';
elsif ifranchise_name >= 516 and
ifranchise_name <= 582 then
franchise_code := 'VC';
elsif ifranchise_name >= 701 and
ifranchise_name <= 799 then
franchise_code := 'NW';
else
franchise_code := NULL;
end if;
return franchise_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(-20599,substr(StoO_errmsg,1,100));
end;
Any idea ??? Thank you !!
Leslie
__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Leslie Lu
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
