Use of Variable returns no rows

[soniaarora]

Hi, I have a query.
The problem is that the SQL was returning an ORA-1403, no data found even though data existed for all tables.I
managed to sort it out by removing any use of variables within the outer-join statements, but I do not understand why the original SQL did not work.
Can one of you explain why when using a variable as part of an outer-join it returns no rows?

[Tarry]

Sonia,
you'll have to post that query here.
I mean it could be anything..
1)You might be trying to access tables where you have no privilege on, it will have tables but won't show it.
2)If the query involved some rownum (select ....where rownum..) sort.

You know specifics.

[soniaarora]

Original:

SELECT
>
> WHERE sdh.parent_table = 'SD_MESSAGE'
> AND sdh.parent_ref = Spq->reference[pSpq->arr_ptr]
> AND sgi.parent_table = 'SD_MESSAGE'
> AND sgi.parent_ref = Spq->reference[pSpq->arr_ptr]
> AND sgri.parent_table (+) = 'SD_MESSAGE'
> AND sgri.parent_ref (+) = Spq->reference[pSpq->arr_ptr]
> AND ssi.parent_table (+) = 'SD_MESSAGE'
> AND ssi.parent_ref (+) = Spq->reference[pSpq->arr_ptr]
> AND cust.parent_table (+) = 'SD_MESSAGE'
> AND cust.parent_ref (+) = Spq->reference[pSpq->arr_ptr]
> AND cust.add_type (+) = 'CUSTODIAN'
> AND subagent.parent_table (+) = 'SD_MESSAGE'
> AND subagent.parent_ref (+) = Spq->reference[pSpq->arr_ptr]
> AND subagent.add_type (+) = 'SUBAGENT'
> AND sipd.parent_table (+) = 'SD_MESSAGE'
> AND sipd.parent_ref (+) = Spq->reference[pSpq->arr_ptr]
> AND sed.parent_table (+) = 'SD_MESSAGE'
> AND sed.parent_ref (+) = Spq->reference[pSpq->arr_ptr]



Modified which runs:
>
> SELECT
>
> WHERE sdh.parent_table = 'SD_MESSAGE'
> AND sdh.parent_ref = Spq->reference[pSpq->arr_ptr]
> AND sgi.parent_table = 'SD_MESSAGE'
> AND sgi.parent_ref = sdh.parent_ref
> AND sgri.parent_table (+) = 'SD_MESSAGE'
> AND sgri.parent_ref (+) = sdh.parent_ref
> AND ssi.parent_table (+) = 'SD_MESSAGE'
> AND ssi.parent_ref (+) = sdh.parent_ref
> AND cust.parent_table (+) = 'SD_MESSAGE'
> AND cust.parent_ref (+) = sdh.parent_ref
> AND cust.add_type (+) = 'CUSTODIAN'
> AND subagent.parent_table (+) = 'SD_MESSAGE'
> AND subagent.parent_ref (+) = sdh.parent_ref
> AND subagent.add_type (+) = 'SUBAGENT'
> AND sipd.parent_table (+) = 'SD_MESSAGE'
> AND sipd.parent_ref (+) = sdh.parent_ref
> AND sed.parent_table (+) = 'SD_MESSAGE'
> AND sed.parent_ref (+) = sdh.parent_ref;