Problem
Here is the select SQL statement:
SELECT YEAR,
PERIOD,
NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
START_DATE, --日期
NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC
And I would to get the first record from the result set.
Therefore, I add rownum=1 in my where clause.
Owning to I need to do union with another select statement, so I move it into subquery as bellows:
SELECT *
FROM
(SELECT YEAR,
PERIOD,
NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
START_DATE, --日期
NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231' AND ROWNUM=1
ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
UNION ALL .....
But I get this record!
Root Cause
This unexpected result result from the SQL query order of operations, the order is:
FROM clause --> WHERE clause --> GROUP BY clause --> HAVING clause --> SELECT clause -->ORDER BY clause
If we marked rownum=1 and order by cluase
SELECT *
FROM
(SELECT YEAR,
PERIOD,
NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
START_DATE, --日期
NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'-- AND ROWNUM=1
--ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC
)
UNION ALL .....
We can find the first record is '1021226' not '1021227', if we marked rownum=1 and order by cluase. Owing the query order operation is from --> where --> select --> order by, 'rownum=1' have already get the first record before order by. So it's the reason why we get the unexpected result.
Solution
We need to get the result which had already been sorted, and apply rownum=1 to get the first record.
SELECT *
FROM
(SELECT YEAR,
PERIOD,
NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
START_DATE, --日期
NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
WHERE ROWNUM=1
UNION ALL .....
Reference
[1] http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm
