Hi Tom,

Whenever i have a problem i seem to find the solution in your site. But the problem i am posting is typical. I did find the solution for this as well in your site. But i am not able to work it out because of the requirement. 

Problem:

I have the below query for which i have to specify the Order by clause. Till now i used to set the column position (say Order by1 etc). Now the requirement is such that the sort has to be case insensitive. So for strings i tried to specify Upper(column_name). But it is returning the ORA-01785. Kindly help me. I am posting my query. Oracle version is 9.2.1.0. O/s WIN2000

SELECT c.contact_id, co.auth_level_ind, ic.last_name, ic.first_name,cc.name,
        (SELECT last_name ||', '|| first_name FROM SECURED_INTERNAL_USER sr, CONTACT_OWNERSHIP ct
         WHERE sr.secured_user_id=ct.secured_user_id and ct.auth_level_ind='Owner' and ct.contact_id=c.contact_id) "Owner",
        (SELECT dchoicename FROM CHOICELISTITEM WHERE dchoiceid=(select country_id
        from location where contact_id=c.contact_id and address_indicator='A')) "Country",
        c.email_address, c.telephone_no,to_char(c.record_date,'DD-Mon-YYYY'), cc.company_contact_id,
        (SELECT cw.secured_user_id FROM CONTACT_OWNERSHIP cw WHERE cw.auth_level_ind='Owner' AND cw.contact_id=c.contact_id)
FROM CONTACT c, INDIVIDUAL_CONTACT ic, COMPANY_CONTACT cc,  SECURED_INTERNAL_USER su, CONTACT_OWNERSHIP co
where c.contact_id=ic.contact_id
and ic.company_contact_id=cc.company_contact_id(+)
and c.contact_id=co.contact_id
and co.secured_user_id=su.secured_user_id
and c.current_status_ind='Active'
and (co.auth_level_ind='Designated Reader' OR co.auth_level_ind='Owner' OR co.auth_level_ind='Designated Author')
and co.secured_user_id='AL3201'
UNION
SELECT c.contact_id, co.auth_level_ind, null,null, cc.name,
        (SELECT last_name ||', '|| first_name FROM SECURED_INTERNAL_USER sr, CONTACT_OWNERSHIP ct
         WHERE sr.secured_user_id=ct.secured_user_id and ct.auth_level_ind='Owner' and ct.contact_id=c.contact_id) "Owner",
        (SELECT dchoicename FROM CHOICELISTITEM WHERE dchoiceid=l.country_id) "Country",
        c.email_address, c.telephone_no,to_char(c.record_date,'DD-Mon-YYYY'), cc.company_contact_id,
    (SELECT cw.secured_user_id FROM CONTACT_OWNERSHIP cw WHERE cw.auth_level_ind='Owner' AND cw.contact_id=c.contact_id)
FROM CONTACT c, COMPANY_CONTACT cc, LOCATION l, SECURED_INTERNAL_USER su, CONTACT_OWNERSHIP co
WHERE c.contact_id=cc.company_contact_id
AND c.contact_id=co.contact_id
AND co.secured_user_id=su.secured_user_id
AND c.postal_address_preference=l.location_id(+)
AND c.current_status_ind='Active'
AND (co.auth_level_ind='Designated Reader' OR co.auth_level_ind='Owner' OR co.auth_level_ind='Designated Author')
and co.secured_user_id='AL3201'
order by upper(cc.name)
SQL>/
order by upper(cc.name)
         *
ERROR at line 31:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Thank you.
Nav
 

<b>use an inline view</b>


ops$tkyte@ORA920PC> select cc.ename
  2    from scott.emp cc
  3   union
  4  select cc.ename
  5    from scott.emp cc
  6   order by upper(ename)
  7  /
 order by upper(ename)
          *
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
 
 
ops$tkyte@ORA920PC> select *
  2    from (
  3  select cc.ename
  4    from scott.emp cc
  5   union
  6  select cc.ename
  7    from scott.emp cc
  8         )
  9   order by upper(ename)
 10  /
 
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
 
14 rows selected.
 
ops$tkyte@ORA920PC>
 

Tom,

I had the following query :

select src_intfc_inst, effective_date, count(*) from securitydbo.fx_rates
where effective_date > '10-NOV-2003'
group by src_intfc_inst, effective_date

src_intfc_inst effective_date count(*)
68 Nov-14-2003 27889
68 Nov-16-2003 30625
68 Nov-12-2003 27889
68 Nov-13-2003 27889
68 Nov-17-2003 30625
68 Nov-15-2003 30625

I was always under the impression that a GROUP BY in a query automatically sorts the records (from some of the Oracle books I read earlier). But here the data is not sorted according to src_intfc_inst and effective_date.
1. Can you explain me under what situations a GROUP BY performs an automatic sort without the need to use the ORDER BY clause
2. Pls. clarify if my understanding is true or not

Hi Tom,

Thanks. It worked. You are Great.

Nav

Hi Tom,

How to specify the column that is used in inline query in the order by clause. For example

select * from(
select c.contact_id,(select dchoicename from choicelistitem where dchoiceid=(select country_id from location where location_id=c.postal_address_preference)) from contact c)
order by upper(dchoicename)
/
how can i specify the Order by clause on dchoicename as the column is part of an inline query. Kindly help me.

Thank you.
Nav.

Hi Tom,

Thanks again.It worked. Hope i'll get a chance to post a question to you directly.

Thank you.
Nav.

1.select sum(a),b,c,d
from t
group by b,c,d

2.select sum(a),b,c,d
from t
group by b,c,d
order by 2,3,4

Tom what will be the difference in the sorting done in the above two cases.

Group by does its own sorting, will it be different from the one done by order by