Oracle decode using partial string of codeset value-CodePudding

I need a code that will let me match a partial string of a school name gr.schoolname to c.displayvalue of our codeset PSSDPriorSchools. The gr.schoolname is just the school name, the PSSDPriorSchools is the school name with the (school division) following it. I need to match the gr.schoolname to the c.displayvalue of the PSSDPriorSchools to be able to retrieve the c.code that corresponds with the c.displayvalue.

Select
s.ID as ID,s.LASTFIRST as LASTFIRST,s.STUDENT_NUMBER as STUDENT_NUMBER,
decode(upper(gr.schoolname), c.code, c.displayvalue) as credit_schools
from 
                     STUDENTS s,
                     codeset c,
                     storedgrades gr
where
                     gr.schoolname= C.displayvalue
                     and c.codetype = 'PSSD_PriorSchools'
                     and gr.studentid=s.id

BUT our gr.schoolname only contains the first part of the string of c.displayvalue.

For example if I need to decode Pilot Mound School to a c.code number of 1301.

In our code set the displayvalue of this school contains a school division at the end like this:

Pilot Mound School (PRAIRIE SPIRIT SCHOOL DIVISION).

So I need a code that would do this:

Select
s.ID as ID,s.LASTFIRST as LASTFIRST,s.STUDENT_NUMBER as STUDENT_NUMBER,
decode(upper(***( gr.schoolname= text before the first ' (') of c.displayvalue)***, c.code,     c.displayvalue) as credit_schools
where
                      gr.schoolname= C.displayvalue
                      and c.codetype = 'PSSD_PriorSchools'
                      and gr.studentid=s.id

I tried it as decode(gr.schoolname,(SUBSTR(gr.schoolname, 0, INSTR(c.displayvalue, '(')-1)),c.code,c.displayvalue) but I can not get it to work.

PSSDPriorSchools looks like:

Code	Display Value	Description Reported Value
1001	Harrow School (WINNIPEG SCHOOL DIVISION)	1001
1002	Educational Support Services (ST. JAMES-ASSINIBOIA SCHOOL DIVISION)	1002
1003	Woodland Colony School (PORTAGE LA PRAIRIE SCHOOL DIVISION)	1003
1006	Mafeking School (SWAN VALLEY SCHOOL DIVISION)	1006
1007	George Fitton School (BRANDON SCHOOL DIVISION)	1007

and I need to decode the Schoolname to match the c.displayvalue so I can get the c.code

Id	LastfirstAscending	Student Number	Schoolname
2986	Abellera, Ana Carissa Evangelista	12945	St. Claude School Complex
2987	Abellera, John Allen Evangelista	12947	Prairie Mountain High School

CodePudding user response：

our gr.schoolname only contains the first part of the string of c.display value.

Then the join condition gr.schoolname= C.displayvalue will never match the rows as they are not equal. You appear to need to join the tables on the start of the string and not on the entire string; something like:

Select s.ID,
       s.LASTFIRST,
       s.STUDENT_NUMBER,
       decode(
         gr.school_name,
         'Pilot Mound School', 1301
       ) as credit_schools
from   codeset c
       INNER JOIN storedgrades gr
       ON C.displayvalue LIKE gr.schoolname || '%'
       INNER JOIN STUDENTS s
       ON gr.studentid = s.id
where  c.codetype = 'PSSD_PriorSchools'

CodePudding user response：

I got this to work.

Select s.ID,
       s.LASTFIRST,
       gr.schoolname,
       c.displayvalue,

  decode(c.displayvalue, c.displayvalue, c.code) as decoded_school

from   

        students s,
        storedgrades gr
-- this code next line is supposed to join gr.schoolname to any c.displayvalue that has gr.schoolname in it
left join codeset c on to_char(upper(gr.schoolname)) LIKE (upper(NVL(SUBSTR(c.displayvalue, 0, INSTR(c.displayvalue, '(')-1), c.displayvalue)) || '%')
--left join codeset c on upper(gr.schoolname) LIKE ('%' || upper(c.displayvalue) || '%')      
where  
c.codetype = 'PSSD_PriorSchools' and
gr.studentid=s.id

group by
s.ID,
       s.LASTFIRST,
      
       gr.schoolname,
       c.code,
       c.displayvalue

which lets me match by gr.schoolname to the c.displayvalue then decode to get my school code c.code.
enter image description here