query returns no rows selected [message #9496] |
Sun, 16 November 2003 02:03 |
Harish Perumal
Messages: 1 Registered: November 2003
|
Junior Member |
|
|
Hi all,
I have a query on Oracle 9i database, running on solaris.
SELECT MYTABLE.NAME, MYTABLE.STEP
FROM MYTABLE WHERE
(MYTABLE.NAME IN ('NAME_1','NAME_2','NAME_3','NAME_4') )
AND MYTABLE.STATUS <> 'R' AND MYTABLE.NAME
IN (SELECT DISTINCT NAME FROM MYTABLE
WHERE (STEP = 'MYSTEP1'))
The query fetches NAMEs that has passed trough SETP 'MYSTEP1' where NAME is either ('NAME_1','NAME_2','NAME_3','NAME_4').
This query returns now rows selected. But there are rows present for the condition.
When the number of names in INLIST is reduced to 3 the query works properly ( returns the rows satisfying the condition).
Is there a workaround for this problem.
Thanks in advance,
Regards,
Harish Perumal.
|
|
|
Re: query returns no rows selected [message #9497 is a reply to message #9496] |
Sun, 16 November 2003 07:11 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
You may have luck with SELECT mt.name
, mt.step
FROM mytable mt
WHERE mt.name IN ('NAME_1','NAME_2','NAME_3','NAME_4')
AND mt.status <> 'R'
AND EXISTS (SELECT NULL
FROM mytable mt2
WHERE mt2.step = 'MYSTEP1'
AND mt2.name = mt.name) If that doesn't work, then which predicate is restricting the result set to the null set?
Good luck, Harish.
Art.
|
|
|