The Subquery which returns multiple rows in Oracle SQL

Question

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,

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

Unrelated, but: if columnName is a numeric column, don't compare it to a string value '123456' is a character literal 123456 is a number — user330315
– user330315, Commented Jan 12, 2015 at 7:30

Lalit Kumar B · Accepted Answer · 2015-01-12 07:21:00Z

1

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.

answered Jan 12, 2015 at 7:21

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oracle Nerd Over a year ago

Hi. Thanks for the quick answer. I know which subquery returns multiple rows. I want to know which value in that particular subquery is fetching two rows. The values of Table1.ColumnName are practically huge and they are dynamically fetched from another query. The query will run for like 100000 values.

Lalit Kumar B Over a year ago

That is what I said, to check which value returns multiple rows, you need to execute the count sql. What you could do is, create a function, pass the values dynamically to the function and catch the exception for the values which return an error.

Nick.Mc Over a year ago

Yes. Try the query as posted. It tells you exactly what you asked for - it shows which value (colum_name) returns more than one row. If you need something else you may need to explain further.

Collectives™ on Stack Overflow

The Subquery which returns multiple rows in Oracle SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related