Re: Functions in SQL - very odd problem.

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1113843554.624962_at_yasure...
> 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.

Hey, you can point as much out as you like... It was written by one of our customers, and I'm just the support manager. Problem was it all started after they upgraded to our latest revision (and Oracle 9i at the same time) so naturally they point the finger at us.

I had my own team look at it as soon as they woke up, and they soon had it fixed. Mind you, it was nothing to do with the things you pointed out.

Cheers,
Steve S Received on Mon Apr 18 2005 - 12:30:49 CDT