我有:
PROCEDURE A
(
inId IN NUMBER,
RC1 OUT SYS_REFCURSOR
) IS
tMessage VARCHAR2(128);
BEGIN
OPEN RC1 FOR
SELECT * FROM
(
SELECT
a.company,
SUM(a.holding_balance) balance
FROM TableNameEntries A
WHERE
A.BATCH_ID = inId
GROUP BY a.company
)
ORDER BY balance DESC ;
EXCEPTION
WHEN OTHERS THEN
tMessage :='Exception ' || SQLCODE || ': ' || SQLERRM;
OPEN RC1 FOR
SELECT tMessage FROM DUAL;
END A;
对于平衡列,我有-1,0,1这样的值。目前它的排序像1 0-1,但我不在乎零,所以我希望它像-1,1,0或1,-1,0
我如何在PL/SQL中做到这一点?我试过了:
SELECT
a.company,
SUM(a.holding_balance) balance
FROM REC.CAG_GL_ENTRIES A
WHERE
A.BATCH_ID = 201311
order by case priority when 0 then 2 else 1 END priority
但它说优先级是无效的标识符。
我怎样才能让它发挥作用?
尝试:
ORDER BY CASE balance
WHEN 0 THEN null
ELSE balance
END
DESC NULLS LAST
在您的案例中有两种方法(第二种更常见,第一种更清楚):
SELECT a.company,
SUM(a.holding_balance) balance
FROM REC.CAG_GL_ENTRIES A
WHERE A.BATCH_ID = 201311
order by abs(balance) desc;
SELECT a.company,
SUM(a.holding_balance) balance
FROM REC.CAG_GL_ENTRIES A
WHERE A.BATCH_ID = 201311
order by decode(balance,1,1,-1,1,2);
ORDER BY SIGN(ABS(balance)) DESC, balance
ABS()将所有的消极因素转化为积极因素。SIGN()将0变为0,>0变为1(以及<0变为-1)。与DESC相结合,这将把所有的零放在末尾,并将所有的非零放在上面,没有特定的顺序。然后我再次按余额排序,以排列非零。
您也可以使用
order by balance * balance desc