Hi all,

I have another little issue I am unable to share the database that the following query uses because it is private but I am trying to return 24 rows using a corrilated sub-query when a rules is broker but I am getting 27 results. Can anyone see an issue with my code that might be the cause?

SELECT
b.first_name||' '||b.last_name "Broker",
SUBSTR(sh1.first_name||' '||sh1.last_name, 1, 20) "Shareholder"
, t1.trade_id as trade
FROM
brokers b
LEFT OUTER JOIN
trades t1
ON b.broker_id=t1.trade_id
LEFT OUTER JOIN
share_holders sh1
ON t1.share_holder_id=sh1.share_holder_id
LEFT OUTER JOIN
share_holder_shares shs1
ON sh1.share_holder_id=shs1.share_holder_id
WHERE
t1.trade_id IN
(
SELECT
t3.trade_id
FROM
share_holder_shares shs3
INNER JOIN
trades t3
ON shs3.share_holder_id=t3.share_holder_id
WHERE
shs3.amount<0 OR t3.share_amount>50000 AND
t1.trade_id=t3.trade_id AND
TO_CHAR(t1.transaction_time, 'yy-mm-dd') = TO_CHAR(t3.transaction_time, 'yy-mm-dd')
GROUP BY
t3.trade_id
)
AND
t1.share_amount>50000
OR
shs1.amount<0
;

Thank you for any help
W.vie

Hi,

I would "flatten out" this using the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm

Or, un-nest the correlated subquedry:

http://www.dba-oracle.com/t_subquery_un_nesting.htm

Also, see here, tips for correlated subqueries:

http://www.dba-oracle.com/t_tuning_sql_subqueries.htm

But there are other ways to un-nest subqueries and starting with Oracle 9i, Oracle will automatically un-nest some sub-queries:

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

• IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause