Re: Any Ideas

Try changing the SELECT value slightly by adding zero and see if that has any impact.

On 7/19/07, Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com> wrote:
>
>
> We have the query below running in a 9.2.0.6 DB
> We put the query into a 9208 instance and the Distinct works, looks as
> if there's a bug at play
>
> SELECT shift.shiftid
> FROM job, shift, lkshiftmachinestage, operation
> WHERE job.actualstartdt <= shift.todt
> AND job.actualenddt >= shift.fromdt
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
> AND job.jobid = operation.jobid
> AND lkshiftmachinestage.shiftid = shift.shiftid
> AND shift.shiftid = 24548
>
> And it returns 255 identical numbers. When I put in a distinct
>
> SELECT DISTINCT shift.shiftid
> FROM job, shift, lkshiftmachinestage, operation
> WHERE job.actualstartdt <= shift.todt
> AND job.actualenddt >= shift.fromdt
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
> AND job.jobid = operation.jobid
> AND lkshiftmachinestage.shiftid = shift.shiftid
> AND shift.shiftid = 24548
>
> I get "no rows found". Even tried making it a subquery
>
> select distinct *
>
> from
>
> (
>
> SELECT shift.shiftid
>
> FROM job, shift, lkshiftmachinestage, operation
>
> WHERE job.actualstartdt <= shift.todt
>
> AND job.actualenddt >= shift.fromdt
>
> AND lkshiftmachinestage.machinestageid =
> operation.machinestageid
>
> AND job.jobid = operation.jobid
>
> AND lkshiftmachinestage.shiftid = shift.shiftid
>
> AND shift.shiftid = 24548
>
> )
> And still get nothing :-S
>
>
> TIA
> Larry
> *************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be
> legally privileged.
>
> If the reader of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited.
>
> If you have received this communication in error, please resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank you.
> *************************************************************************
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l