Re: Functions in SQL - very odd problem.

SteveS wrote:

> Can anyone help with a mysterious problem that has arisen since 'upgrading'
> from 8 to 9.2.0.4?
>
> The situation is this:
>
> Queries that worked fine under 8 are now producing *really* strange results
> under 9.2.0.4. The queries all involve functions, including built-in
> functions
> like NVL. The strangeness is that less than the expected number of rows is
> returned *or* and ORA-01422 is generated. Here's an example:
>
>
> -- This query yielded 54 records
>
>
> select distinct worklistname Worklist_Name, worklistcreatets Date_Created,
> u2.fullname Created_by,samplename, u2.fullname Closed_By, audittimestamp
> Date_Closed
> from wkls_histx, lmsuser u1, lmsuser u2
> where audittimestamp between '&StartDate' and '&EndDate'
> and samplename not like 'JAN-%'
> and fullprocname not like '%OBMCLIBRARY%'
> and fullprocname not like '%MICROLIBRARY%'
> and fullprocname not like '%OBMMLIBRARY%'
> and assignedanalyst not in ('None','Not Defined')
> and u2.fullname not like 'ChemLMS%'
> -- and nvl(u1.state,'NULL') in ('NULL','*','d')
> -- and nvl(u2.state,'NULL') in ('NULL','*','d')
> and u1.state <> '+'
> and u2.state <> '+'
> and assignedanalyst=u1.name
> and auditusername=u2.name

I hate to not help you with your problem and be negative all in the same post ... but your query looks like a summarization of a lot of bad practices all merged into a single query.

NOT LIKE
NOT IN
using 'NULL' as a string
using an operator '+' as a value
using two keywords as values '+' and '*'

SELECT keyword
FROM v$reserved_words
WHERE LENGTH(keyword) = 1;

I would suggest you consider rewriting the entire query and, if possible, a bit a rearchitecture too.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)