Trip to Paraiso

Gordon's org search
ere's my orgs program. The @detail just calls table_detail looking for the passed column_name. Table_detail is where the codes are translated to descriptions. PROMPT ********************************************************** PROMPT You can choose to leave any of the following prompts blank PROMPT The search uses "Like" EXCEPT that you can choose whether PROMPT to search for the exact org_cde or not PROMPT Org_cde will have leading zeros removed PROMPT Name searches are case-insensitive PROMPT ********************************************************** Accept id_num char prompt 'Enter ID_NUM: ' Accept name char prompt 'Enter any part of name: ' Accept org_cde_exact prompt 'Do you want to search for the exact org_cde? ( def = o ) ' Accept org_cde char prompt 'Enter ORG_CDE ( or ceeb code): ' def = o ) ' Accept org_cde char prompt 'Enter ORG_CDE ( or ceeb code): ' @detail_param org_type '' '' Accept org_type char prompt 'Enter ORG_TYPE: ' @detail_param school_type '' '' Accept school_type char prompt 'Enter SCHOOL_TYPE: ' set feed off REM This is run if &org_cde_exact is 'N' SELECT id_num, NAME_FORMAT(id_num,'business_name') bname, org_cde, org_type, school_type FROM org_master o WHERE SUBSTR(LPAD(RTRIM(org_cde),6,'0'),1,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),2,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),3,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),4,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),5,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),6,1) between '0' and '9' AND RTRIM(TO_CHAR(TO_NUMBER(org_cde))) LIKE ('%'||RTRIM(TO_CHAR(TO_NUMBER('&org_cde')))||'%') AND UPPER(RTRIM(nvl(org_type,'zzz'))) LIKE UPPER('%&org_type%') AND UPPER(RTRIM(nvl(school_type,'zzz'))) LIKE UPPER('%&school_type%') AND DECODE(UPPER('&org_cde_exact'),'','N',UPPER('&org_cde_exact')) = 'N' AND UPPER(NAME_FORMAT(id_num,'business_name')) LIKE UPPER('%&name%') AND TO_CHAR(id_num) LIKE '&id_num%' / REM This is run if &org_cde_exact is 'Y' SELECT id_num, NAME_FORMAT(id_num,'business_name') bname, org_cde, org_type, school_type FROM org_master o WHERE SUBSTR(LPAD(RTRIM(org_cde),6,'0'),1,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),2,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),3,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),4,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),5,1) between '0' and '9' AND SUBSTR(LPAD(RTRIM(org_cde),6,'0'),6,1) between '0' and '9' AND RTRIM(TO_CHAR(TO_NUMBER(org_cde))) = RTRIM(TO_CHAR(TO_NUMBER('&org_cde'))) AND UPPER(RTRIM(nvl(org_type,'zzz'))) LIKE UPPER('%&org_type%') AND UPPER(RTRIM(nvl(school_type,'zzz'))) LIKE UPPER('%&school_type%') AND DECODE(UPPER('&org_cde_exact'),'','N',UPPER('&org_cde_exact')) = 'Y' AND UPPER(NAME_FORMAT(ID_NUM,'business_name')) LIKE UPPER('%&name%') AND TO_CHAR(id_num) LIKE '&id_num%' / prompt set feed on
Posted at 12:19 #

What is an FICE code?
http://roie.schev.edu/glossary.asp?myterm=30&alpha=F -- A federal agency is behind this system. There's a code for both undergrad and postgrad institutions (GC's is 001799), but it's different from the CEEB codes.
Posted at 12:05 #

FICE code listing
www.stat-rpts.northcarolina.edu/SDFTECH/ficea.pdf -- A PDF document listing many (all?) FICE codes
Posted at 12:02 #