Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: SQL Server to Oracle Conversion

    I was wondering if anyone has had any experience with SQL Server to Oracle conversions. I am trying to convert a statement in SQL Server that says:

    SELECT DISTINCT TOP 50 name "Name", convolute(lmatch, 'SMITH' ,1,4,16) score

    FROM entname
    WHERE
    exists ( select FID from ventity where entname.lid = ventity.lid and ventity.fid <> 0 ) and
    name like 'S%'

    ORDER BY score,name

    Where convolute is a function that weights string matches against each other, returning a score. The higher the score, which can be infinitely high, the closer the match to the search string.
    My translated Oracle SQL looks like this:

    SELECT DISTINCT name "Name", convolute( lmatch, 'SMITH' ,1,4,16) score
    FROM entname
    WHERE
    EXISTS ( select FID from ventity, ENTNAME where entname.lid = ventity.lid and ventity.fid <> 0 ) AND
    name like 'S%'
    AND rownum<=150
    ORDER BY score desc,name


    The problem is that in Oracle 9i, the query gets run until it gets up to whatever value it sees for rownum. It is my understanding that in SQL Server and Oracle 7 that the entire query gets run, ORDER BY-ed, then the result set is cut down to a proper length. Because of various performance issues, it is important that I only return a limited set of data here.

    Any ideas?

    -D

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    SELECT DISTINCT name "Name", convolute( lmatch, 'SMITH' ,1,4,16) score
    FROM entname e, ventity v
    WHERE e.lid = v.lid
    AND v.fid <> 0
    AND name like 'S%'
    ORDER BY score desc,name

    to restrict the resultset try

    select * from
    (
    SELECT DISTINCT name "Name", convolute( lmatch, 'SMITH' ,1,4,16) score
    FROM entname e, ventity v
    WHERE e.lid = v.lid
    AND v.fid <> 0
    AND name like 'S%'
    ORDER BY score desc,name
    )
    where rownum<150

    if performance is slow check your explain plan (remember to analyze your tables) as you might need some indexes.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •