Re: How to escape hints in PL/SQL and use results of a sort to limit comparisons

From: sidey <sideyt_at_hotmail.com>
Date: 30 Aug 2006 05:23:00 -0700
Message-ID: <1156940580.479160.157500@74g2000cwt.googlegroups.com>

Thank you, Andy - I was working pretty hard yesterday trying to get this 3 page SQL script translated to PL/SQL so I could use frequency information about rows that have duplicate values in the location field to limit the the range of rows that have to be checked by Oracle. (Shown clumsily here using rowid but using a sequence lower down. I sort the table before creating the sequence - am at home so cannot type that little gem that was devised yesterday)

  I plan to check the sequence seq of each row (permanently stored in a field) by:

            t1.seq < t2.seq +n and t1.seq > t2.seq -n where n = frequency -1)

So if I have a node (location) that occurs 10 times in the table and all other nodes have an equal or lesser frequency , Oracle will only need to consider 9 other rows relative to the current row's sequence.

I will remember to look not only at the last statement but also at the next line!!

               It wonderful to feel one has friends rather than getting comments about DB don't sort - they can use the result of a sort as described above...

                 Sidey  (it rhymes with Heidi)

Andy Hassall wrote:
> On 29 Aug 2006 15:41:50 -0700, "sidey" <sideyt_at_hotmail.com> wrote:
>
> >Oracle SQL Developer puts a little red nderscore right after ujvc
> >
> >ujvc := '/' || CHR(42) || '+bypass_ujvc */';
> >
> > sql_stmt := 'UPDATE ('
> > || 'SELECT' || ujvc || ' t1.' || c1 || ' c1 '
> > ^ _____________________little red thing
>
> > || 'from ' || mytab ' t1, ' || mytab ' t2 '
> ^ don't you mean here?
>
> > || 'where t1.rowid > t2.rowid '
> > || 'and t1.qsn = t2.qsn '
> > || 'and t1.qsn2 = t2.qsn2 '
> > || ') ' ||
> > 'set c1 = -1';
> >
> > and won't compile the procedure:
> >
> >Error(135,26): PLS-00103: Encountered the symbol " t1, " when expecting
> >one of the following: . ( * @ % & = - + ; < / > at in is mod
> >remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or
> >like LIKE2_ LIKE4_ LIKEC_ between || member SUBMULTISET_
>
> The error message mentions " t1, ", which is on the line below, and is indeed
> next to a parse error.
>
> If SQL Developer is putting the mark on the line you mention then it's got an
> off-by-one bug. Just tried it here, and yes, it puts the error on the wrong
> line. I don't to use Oracle SQL Developer, but if it bothers you then you
> should report it as a bug to Oracle.
>
> (Incidentally, PL/SQL Developer reports the error in the correct place)
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Wed Aug 30 2006 - 07:23:00 CDT