Home » SQL & PL/SQL » SQL & PL/SQL » I need to sort the record omiting negative sign. (sql*plus)
| I need to sort the record omiting negative sign. [message #386843] |
Tue, 17 February 2009 02:44  |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
Hi,
I am new to this site, I need you help to solve my problem. I am using a query to fetch a value from table for example :-
There are 5 records with the value 40,30,14,10,-35 :-
Select mark from exam order by mark desc;
Now the output it shows like this :-
40
30
14
10
-35
But i need to omit the negative sign and sort the value to show the value like :
40
-35
30
14
10
If any one has a solution, please let me know. thanks in advance.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386863 is a reply to message #386856] |
Tue, 17 February 2009 03:20  |
 |
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
|
Senior Member |
|
|
| good wrote on Tue, 17 February 2009 10:14 | Thanks. It works for single select statement, but i do have a UNION ALL to combine 2 queries. it gives a error message.
|
As we don't have access to your system(s) and some of use aren't psychic we need more information.
| good wrote on Tue, 17 February 2009 10:14 |
what to do?
|
Maybe post the query and the errormessage ?
EDIT: Maaher is one of the psychic's around here and posted what could be a solution 
[Updated on: Tue, 17 February 2009 03:21] Report message to a moderator |
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386865 is a reply to message #386843] |
Tue, 17 February 2009 03:22 |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
This is my orginal query, tried with ABS functio to it.
SELECT TRIM (holding_type) holding_type, as_of_date, detail_value,
holding_type_name
FROM gws_issue_compositions_v comp
WHERE instr_id = '82 '
AND holding_type NOT IN
('AVERAGE COUPON', 'BOND QUALITY', 'CREDIT QUALITY',
'COUPON RATE BREAKDOWN', 'MATURITY BREAKDOWN')
AND as_of_date =
(SELECT MAX (as_of_date)
FROM gws_issue_compositions_v comp_max
WHERE comp_max.instr_id = comp.instr_id
AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
AND TRUNC (as_of_date) <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
AND holding_type IN ('CURRENCY DISTRIBUTION')
UNION ALL
SELECT TRIM (holding_type) holding_type, as_of_date, detail_value,
holding_type_name
FROM gws_issue_compositions_v comp
WHERE instr_id = '82 '
AND holding_type IN ('AVERAGE COUPON')
AND as_of_date =
(SELECT MAX (as_of_date)
FROM gws_issue_compositions_v comp_max
WHERE comp_max.instr_id = comp.instr_id
AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
AND as_of_date <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
ORDER BY 1, abs(detail_value) DESC, 4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386880 is a reply to message #386876] |
Tue, 17 February 2009 03:37 |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
| MarcS wrote on Tue, 17 February 2009 03:31 | | Michel Cadot wrote on Tue, 17 February 2009 10:27 | And the error message is self-explanatory.
Regards
Michel
|
Yes and ... no, in this case.
I notice the mix of position and column-name the ORDER BY, which should work.
I count 4 attributes in the SELECT-list, so the ORDER BY on 4 should work.
So I'm overlooking something, or the information provided isn't complete
|
I have tried by change it like below :-
ORDER BY holding_type, abs(detail_value) DESC, holding_type_name
|
|
|
|
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386884 is a reply to message #386843] |
Tue, 17 February 2009 03:44 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Seems oracle can cope with a mix of numeric and named notation for an order by so long as the named column isn't piped through a function:
SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
2 FROM dual
3 UNION
4 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
5 FROM dual
6 ORDER BY 1, b, 3;
A B C
---------- ---------- ----------
1 1 1
2 1 1
SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
2 FROM dual
3 UNION
4 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
5 FROM dual
6 ORDER BY 1, abs(b), 3;
ORDER BY 1, abs(b), 3
*
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
OP - try specifying all the columns in the ORDER BY by name.
EDIT: spent far too long knocking up that test case obviously.
[Updated on: Tue, 17 February 2009 03:46] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386890 is a reply to message #386843] |
Tue, 17 February 2009 03:48 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Here was me thinking that would work.
Making the union SELECT an inline view does work:
SQL> SELECT * FROM(
2 SELECT ROWNUM a, ROWNUM b, ROWNUM c
3 FROM dual
4 UNION
5 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
6 FROM dual)
7 ORDER BY a, abs(b), c;
A B C
---------- ---------- ----------
1 1 1
2 1 1
SQL>
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386891 is a reply to message #386884] |
Tue, 17 February 2009 03:49 |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
| cookiemonster wrote on Tue, 17 February 2009 03:44 | Seems oracle can cope with a mix of numeric and named notation for an order by so long as the named column isn't piped through a function:
SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
2 FROM dual
3 UNION
4 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
5 FROM dual
6 ORDER BY 1, b, 3;
A B C
---------- ---------- ----------
1 1 1
2 1 1
SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
2 FROM dual
3 UNION
4 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
5 FROM dual
6 ORDER BY 1, abs(b), 3;
ORDER BY 1, abs(b), 3
*
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
OP - try specifying all the columns in the ORDER BY by name.
EDIT: spent far too long knocking up that test case obviously.
|
i have tried you thought also, still the same problem .
ORDER BY holding_type, abs(detail_value) DESC, holding_type_name
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386892 is a reply to message #386890] |
Tue, 17 February 2009 03:52 |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
| cookiemonster wrote on Tue, 17 February 2009 03:48 | Here was me thinking that would work.
Making the union SELECT an inline view does work:
SQL> SELECT * FROM(
2 SELECT ROWNUM a, ROWNUM b, ROWNUM c
3 FROM dual
4 UNION
5 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
6 FROM dual)
7 ORDER BY a, abs(b), c;
A B C
---------- ---------- ----------
1 1 1
2 1 1
SQL>
|
Tried you logic also, same error :
SELECT TRIM (holding_type) a, as_of_date b, detail_value c,
holding_type_name d
FROM gws_issue_compositions_v comp
WHERE instr_id = '82 '
AND holding_type NOT IN
('AVERAGE COUPON', 'BOND QUALITY', 'CREDIT QUALITY',
'COUPON RATE BREAKDOWN', 'MATURITY BREAKDOWN')
AND as_of_date =
(SELECT MAX (as_of_date)
FROM gws_issue_compositions_v comp_max
WHERE comp_max.instr_id = comp.instr_id
AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
AND TRUNC (as_of_date) <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
AND holding_type IN ('CURRENCY DISTRIBUTION')
UNION ALL
SELECT TRIM (holding_type) a, as_of_date b, detail_value c,
holding_type_name d
FROM gws_issue_compositions_v comp
WHERE instr_id = '82 '
AND holding_type IN ('AVERAGE COUPON')
AND as_of_date =
(SELECT MAX (as_of_date)
FROM gws_issue_compositions_v comp_max
WHERE comp_max.instr_id = comp.instr_id
AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
AND as_of_date <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
ORDER BY a, abs(c) DESC, d
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386893 is a reply to message #386890] |
Tue, 17 February 2009 03:53 |
good
Messages: 27
Registered: July 2000
|
Junior Member |
|
|
| cookiemonster wrote on Tue, 17 February 2009 03:48 | Here was me thinking that would work.
Making the union SELECT an inline view does work:
SQL> SELECT * FROM(
2 SELECT ROWNUM a, ROWNUM b, ROWNUM c
3 FROM dual
4 UNION
5 SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
6 FROM dual)
7 ORDER BY a, abs(b), c;
A B C
---------- ---------- ----------
1 1 1
2 1 1
SQL>
|
Thanks It works... GREAT !!!!
|
|
|
|
| Re: I need to sort the record omiting negative sign. [message #386896 is a reply to message #386888] |
Tue, 17 February 2009 03:59 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
| good wrote on Tue, 17 February 2009 10:47 | | Maaher wrote on Tue, 17 February 2009 03:41 | Move the ABS() to the select list as a new column. That should do the trick.
MHE
|
i need to solve with out adding new display column.
|
I should have been more clear. You can wrap the select (with union all) in an inner select. Ok, I'll set up a small test case:
WITH table_1 AS
(
SELECT 1 a, -2 b, 3 c FROM dual UNION ALL
SELECT 2 a, 2 b, 2 c FROM dual UNION ALL
SELECT 1 a, -1 b, 1 c FROM dual UNION ALL
SELECT 1 a, 0 b, 0 c FROM dual
),
table_2 AS
(
SELECT 2 a, -5 b, 0 c FROM dual UNION ALL
SELECT 1 a, 12 b, 1 c FROM dual UNION ALL
SELECT 2 a, 7 b, 2 c FROM dual UNION ALL
SELECT 2 a, -1 b, 3 c FROM dual UNION ALL
SELECT 1 a, 3 b, 4 c FROM dual UNION ALL
SELECT 3 a, 0 b, 3 c FROM dual UNION ALL
SELECT 1 a, -2 b, 2 c FROM dual UNION ALL
SELECT 2 a, 8 b, 0 c FROM dual
)
SELECT a
, b
, c
-- small trick: put brackets around the select
FROM
(
SELECT a, b, c
FROM table_1
UNION ALL
SELECT a, b, c
FROM table_2
)
ORDER BY 1, abs(b), 3
/
MHE
Edit: Damn, too late. 
[Updated on: Tue, 17 February 2009 04:00] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Dec 06 01:05:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Comes with the job, I guess...