Oracle-L: Re: Interesting Question - how do we do it? (fwd)

From: Stephen W. Thompson <thompson_at_POBOX.UPENN.EDU>
Date: Thu, 25 Jan 1996 08:53:42 -0500
Message-Id: <9601251951.AA23540@alice.jcc.com>

Curtis D. Carr <ccarr_at_NEBULA.LIB.VT.EDU> wrote:

> > I'm searching the db for resources (a table). I'm looking for:
> > grade(column in resources) = 7, 8, or 9
> > subject(column in resouces linked to a subject table) = algebra

>             or geometry

> > activity(column in resources) = real world problems
> > connections(column in resources) = all

> > I do an or search on everything above. Now, when I get my results
> > back, they want them weighted using a very simple scoring method,
> > where each hit's score = the number of categories of things it
> > matched. I then need to present the results in score ranked
> > order.

This will run SLOWLY, but may do some of what you wanted if you can wait for it. (Caveat, caveat.) My tendency would be to do the individual queries and then do post-processing in a procedural language (even PL/SQL).

select
id,

  decode( grade, 7,1, 8,1, 9,1, 0 ) +
  decode( subject, 'algebra',1, 'geometry',1, 0 ) +
  decode( activity, 'real world problems',1, 0 ) +
  decode( connections, 'all',1, 0 ) "sortvar"

from resources
where
  grade in (7,8,9) OR
  subject in ('algebra','geometry') OR
  activity = 'real world problems' OR
  connections = 'all'
sort by 2 desc

En paz,
Steve
--
Stephen W. Thompson, U. of PA, Data Administration, 215-898-1236, WWW has PGP thompson@isc.upenn.edu URL=http://pobox.upenn.edu/~thompson/index.html Received on Thu Jan 25 1996 - 14:52:02 CST