I need to use order by in a PL/SQL that uses UNION and I hope I can get some suggestions, please.
The PL/SQL returns the following:
CRN | Inv_1 | Inv_2 |
---|---|---|
GBP | -100 | -100 |
GBP | -400 | -400 |
GBP | -500 | -500 |
GBP | -700 | -700 |
GBP | -800 | -800 |
GBP | -10000 | -10000 |
GBP | 100 | 100 |
GBP | 400 | 400 |
GBP | 500 | 500 |
GBP | 700 | 700 |
GBP | 800 | 800 |
GBP | 10000 | 10000 |
Expected Output:
CRN | Inv_1 | Inv_2 |
---|---|---|
GBP | 100 | 100 |
GBP | -100 | -100 |
GBP | 400 | 400 |
GBP | -400 | -400 |
GBP | 500 | 500 |
GBP | -500 | -500 |
GBP | 700 | 700 |
GBP | -700 | -700 |
I don't have an ID to select and order by it and even if I have, I can't use it since it will appear in the table which I can't include in the month-end report.
These values will keep changing so they can't be hard coded to use case statements in order. I tried so many options such as:
ORDER BY Inv_1 DESC, Inv_1 ASC
ORDER BY Inv_1 ASC, Inv_1 DESC
ORDER BY Inv_1 ASC, Inv_2 DESC
ORDER BY Inv_1 DESC, Inv_2 DESC
If I use ABS(Inv_1 DESC), Inv_1
. This throws an error: ORDER BY ITEM MUST BE THE NUMBER OF A SELEC-LIST EXPRESSION.
I'm not using ABS but *-1 instead, since ABS returns the wrong Inv_1/Inv_2
Example:
**Using ABS with UNION doesn't work : error: ORDER BY ITEM MUST BE THE NUMBER OF A SELEC-LIST EXPRESSION. **
Select CRN,inv_1,inv_2 from Acc UNION Select CRN,inv_1,inv_2 from Acc ORDER BY CRN, ABS(inv_1),inv_1 DESC;
CodePudding user response:
You didn't post your query, but - this kind of works for me.
Sample data:
SQL> select * from test;
CRN INV_1 INV_2
--- ---------- ----------
GBP -700 -700
GBP 100 100
GBP 700 700
GBP -100 -100
GBP -500 500
GBP 400 400
GBP 500 500
GBP -400 -400
8 rows selected.
Query with order by
clause:
SQL> select *
2 from test
3 order by crn, abs(inv_1), inv_1 desc;
CRN INV_1 INV_2
--- ---------- ----------
GBP 100 100
GBP -100 -100
GBP 400 400
GBP -400 -400
GBP 500 500
GBP -500 500
GBP 700 700
GBP -700 -700
8 rows selected.
SQL>
[EDIT]
Aha, now I understand (although you still didn't post your query).
If there are two tables which return the result set:
SQL> select * from test;
CRN INV_1 INV_2
--- ---------- ----------
GBP -700 -700
GBP 100 100
GBP 700 700
GBP -100 -100
SQL> select * from test2;
CRN INV_1 INV_2
--- ---------- ----------
GBP -500 500
GBP 400 400
GBP 500 500
GBP -400 -400
SQL>
Then - if you UNION
them and apply order by
something that isn't part of the query - it won't work:
SQL> select crn, inv_1, inv_2
2 from test
3 union
4 select crn, inv_1, inv_2
5 from test2
6 order by crn, abs(inv_1), inv_1 desc;
order by crn, abs(inv_1), inv_1 desc
*
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
SQL>
What can you do? Obviously, add abs(inv_1)
into the query:
SQL> select crn, inv_1, inv_2, abs(inv_1) abs_inv1
2 from test
3 union
4 select crn, inv_1, inv_2, abs(inv_1) abs_inv1
5 from test2
6 order by crn, abs_inv1, inv_1 desc;
CRN INV_1 INV_2 ABS_INV1
--- ---------- ---------- ----------
GBP 100 100 100
GBP -100 -100 100
GBP 400 400 400
GBP -400 -400 400
GBP 500 500 500
GBP -500 500 500
GBP 700 700 700
GBP -700 -700 700
8 rows selected.
SQL>
This works, but - now you have additional column which you don't really want in the output.
Workaround is to use a CTE or a subquery which contains UNION
, but then - in the outer query - you can sort by abs
value:
SQL> with temp as
2 (select crn, inv_1, inv_2
3 from test
4 union
5 select crn, inv_1, inv_2
6 from test2
7 )
8 select crn, inv_1, inv_2
9 from temp
10 order by crn, abs(inv_1), inv_1 desc;
CRN INV_1 INV_2
--- ---------- ----------
GBP 100 100
GBP -100 -100
GBP 400 400
GBP -400 -400
GBP 500 500
GBP -500 500
GBP 700 700
GBP -700 -700
8 rows selected.
SQL>
CodePudding user response:
You can do:
select * from t
order by abs(inv_1), inv_1 desc
Result:
CRN INV_1 INV_2
---- ------- ------
GBP 100 100
GBP -100 -100
GBP 400 400
GBP -400 -400
GBP 500 500
GBP -500 -500
GBP 700 700
GBP -700 -700
GBP 800 800
GBP -800 -800
GBP 10000 10000
GBP -10000 -10000
See running example at db<>fiddle.
As you can see UNION
is not needed to get the ordering you want.