Home » SQL & PL/SQL » SQL & PL/SQL » Trying to sort within a multiple UNION sql
Trying to sort within a multiple UNION sql [message #314057] Tue, 15 April 2008 13:21 Go to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I have the following SQL that works exactly like I want, except for the sort/order by. I would like to order my data by:

dis_acct_unit, dis_account, distrib_date

but I keep getting an Oracle Error 904 INVALID IDENTIFIER

not sure why this is since distrib_date is referenced in the first select statement

many thanks in advance!

select dis_acct_unit, dis_account, invoice, invoice_dte, distrib_date, company, vendor, vendor_vname, account_desc, to_base_amt
 from vwuabdist
where company = 900 and
      distrib_date > '30-SEP-07'
union
select dis_acct_unit, dis_account, null, null, null, null, null, null, null, sum(to_base_amt)
 from vwuabdist
where company = 900 and
      distrib_date > '30-SEP-07'
group by dis_acct_unit, dis_account
union 
select dis_acct_unit, null, null, null, null, null, null, null, null, sum(to_base_amt)
 from vwuabdist
where company = 900 and
      distrib_date > '30-SEP-07'
group by dis_acct_unit
order by dis_acct_unit, dis_account
Re: Trying to sort within a multiple UNION sql [message #314059 is a reply to message #314057] Tue, 15 April 2008 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you use set operator you have to name the order columns by position not by name: "order by 1,2,5"

Regards
Michel
Re: Trying to sort within a multiple UNION sql [message #314104 is a reply to message #314057] Tue, 15 April 2008 15:41 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Also, I think that a UNION ALL should be sufficient in your query. This will save you a sort, as UNION will sort your records, and then you want to sort them again into your own preferential order.

Also, lookup the group by grouping sets syntax. This will allow you to do this in a single query (get the row by row data, and additionally get subtotals on sets of data items).

[Updated on: Tue, 15 April 2008 15:41]

Report message to a moderator

Re: Trying to sort within a multiple UNION sql [message #314107 is a reply to message #314057] Tue, 15 April 2008 15:46 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
klkuab wrote on Tue, 15 April 2008 14:21
I
select dis_acct_unit, dis_account, invoice, invoice_dte, distrib_date, company, vendor, vendor_vname, account_desc, to_base_amt
 from vwuabdist
where company = 900 and
      distrib_date > '30-SEP-07'



This is not the proper way to compare a DATE column in Oracle. You MUST use the TO_DATE function if you are going to use hardcoded strings as you have here. You really should read up on TO_DATE functions as opposed to just accepting that this code may work for you this time, but it surely does not work for others.
TEST10G FOO>l
  1* select 1 from dual where sysdate > '30-SEP-07'
TEST10G FOO>/
select 1 from dual where sysdate > '30-SEP-07'
                                   *
ERROR at line 1:
ORA-01843: not a valid month
Previous Topic: sequence Number
Next Topic: Select Previous Record!?
Goto Forum:
  


Current Time: Mon Dec 05 15:18:09 CST 2016

Total time taken to generate the page: 0.28407 seconds