CODEWITHSUNDEEP
sqloracle-database
Oracle Nerd
Oracle Nerd

Reputation: 154

The Subquery which returns multiple rows in Oracle SQL

I have a complex SQL query with multiple sub queries. The Query returns a very big data. The tables are dynamic and they get updated every day. Yesterday, the query didn't execute, because one of the subqueries returned multiple rows.

The subquery would be something like this. 

Select Value1 from Table1 where Table1.ColumnName = 123456

Table1.ColumnName will be fetched dynamically, nothing will be hardcoded. Table1.ColumnName will be fetched from another subquery which runs perfectly.

My Question would be,

  1. How to find which value in the particular subquery returned two rows.

Upvotes: 2

Views: 1624

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

How to find which value in the particular subquery returned two rows.

You need to check each sub-query whether it returns a single-row or multiple-rows for a value. You can use the COUNT function to verify -

select column_name, count(*) from table_name group by column_name having count(*) > 1

The above is the sub-query for which it checks the count of rows grouped by each value, if any value returns more than one row, that value is the culprit.

Once you get to know which sub-query and respective column is the culprit, you coulkd then use ROWNUM or ANALYTIC functions to limit the number of rows.

Upvotes: 1

Related Questions