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  |
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 #314104 is a reply to message #314057] |
Tue, 15 April 2008 15:41   |
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  |
joy_division
Messages: 4963 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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:40:54 CST 2025
|