Using Max In Subquery.

[shubhi]

I am using max in following way at various places but here i am getting error that single single-row subquery returns more than one row. I made correction in following query by taking max as following max(c.company_name) ,that solves my problem...but that is not the requirement. Can any one help in this case
Please let me know asap to resolve this problem

select max ((select c.COMPANY_NAME
from companies c
where c.COMPANIES_ID in (
select distinct case when (select ta.transport_advis_id from transport_advis ta where ta.PACKING_NOTE_DETAILS_ID = pnd.PACKING_NOTE_DETAILS_ID and ta.deleted='N') is null
Then
Case
When pn.STATUS_CODES_ID = 50717
then
pn.companies_id
when pn.STATUS_CODES_ID = 50716
Then
pn.companies_id
Else
NULL
End
Else
to_number((select ta.carrier from transport_advis ta where ta.PACKING_NOTE_DETAILS_ID = pnd.PACKING_NOTE_DETAILS_ID and ta.deleted='N'))
End Carrier
from
packing_notes pn,
packing_note_details pnd,
order_details od
where pn.packing_notes_id = pnd.packing_notes_id
And pnd.order_details_id = od.order_details_id
and od.orders_id = 288541300)) from companies;

[amr]

you must write after case the name of column

[Dave44]

you must write after case the name of column

actually you dont "have" to alias it... but it sure makes it easier to reference if you need to :)

[Dave44]

I am using max in following way at various places but here i am getting error that single single-row subquery returns more than one row. I made correction in following query by taking max as following max(c.company_name) ,that solves my problem...but that is not the requirement. Can any one help in this case
Please let me know asap to resolve this problem

When you say it is not the requirement, do you mean that it returns different results than what you want?

You can see why its giving the error without the max though right?

your query, as shown here, without the max() in place is saying for every row in the companies table return the results of the this large subquery. now that subquery is to return every row in the companies table where its id is in some list of ids (returned from another subquery).

it is conceptually no different than if i did this:

[138]dave@ORADB> SELECT (SELECT 1
  2          FROM   DUAL
  3          UNION
  4          SELECT 2
  5          FROM   DUAL)
  6  FROM   DUAL;
SELECT (SELECT 1
        *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
 

the point is you can't have multiples rows returned for each row in the companies (source) table (the outer most query). So, yes, placing the max on there would limit the subquery to returning just 1 row which makes it a legal SQL statement.

So for me to help further i need to know what is it exactly that you are wanting returned?

[seraphstream]

try to see:

Oracle Minus Functions

Oracle subqueries

bye