碰到标量子查询中有ROWNUM=1怎么改？
select to_date(o.postdate,'yyyymmdd'),
(select cur.c_code from cur_tbl cur where cur.c_abbr=o.currencycode) as CusNo,
o.dramount,
round(o.dramount *
(select s.exechangerate / 100
from tcsa.uccexchange s
where s.exchangecurrency =
(SELECT T.t_code
FROM tcsa.sap_code_mapping t
where t.typename = 'currency'
and T.sap_code = o.currencycode)
and TO_CHAR(s.issuancedate, 'yyyymmdd') = o.postdate
and rownum = 1),2) AS Debit_Sum_RMB,
o.cramount,
round(o.cramount *
(select s.exechangerate / 100
from tcsa.uccexchange s
where s.exchangecurrency =
(SELECT T.t_code
FROM tcsa.sap_code_mapping t
where t.typename = 'currency'
and T.sap_code = o.currencycode)
and TO_CHAR(s.issuancedate, 'yyyymmdd') = o.postdate
and rownum = 1),2) AS Credit_Sum_RMB
from tcsa.mm_sap_voucher_detail_to o
where o.postdate >= '20170101'
and o.postdate < '20180101' ;
Plan hash value: 1961056669
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1210K| 639M| | 153K (1)| 00:30:48 | | |
|* 1 | TABLE ACCESS FULL | CUR_TBL | 1 | 12 | | 3 (0)| 00:00:01 | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
|* 3 | FILTER | | | | | | | | |
|* 4 | VIEW | | 31006 | 2240K| | 778 (1)| 00:00:10 | | |
|* 5 | WINDOW SORT PUSHED RANK| | 31006 | 2815K| 6632K| 778 (1)| 00:00:10 | | |
| 6 | REMOTE | UCCEXCHANGE | 31006 | 2815K| | 111 (1)| 00:00:02 | CLONE | R->S |
| 7 | REMOTE | SAP_CODE_MAPPING | 1 | 240 | | 2 (0)| 00:00:01 | CLONE | R->S |
|* 8 | COUNT STOPKEY | | | | | | | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | VIEW | | 31006 | 2240K| | 778 (1)| 00:00:10 | | |
|* 11 | WINDOW SORT PUSHED RANK| | 31006 | 2815K| 6632K| 778 (1)| 00:00:10 | | |
| 12 | REMOTE | UCCEXCHANGE | 31006 | 2815K| | 111 (1)| 00:00:02 | CLONE | R->S |
| 13 | REMOTE | SAP_CODE_MAPPING | 1 | 240 | | 2 (0)| 00:00:01 | CLONE | R->S |
| 14 | REMOTE | MM_SAP_VOUCHER_DETAIL_TO | 1210K| 639M| | 153K (1)| 00:30:48 | CLONE | R->S |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUR"."C_ABBR"=SYS_OP_C2C(:B1))
2 - filter(ROWNUM=1)
3 - filter("EXCHANGECURRENCY"= (SELECT /*+ */ "T"."t_code" FROM "A2" WHERE "T"."TYPENAME"='currency' AND
"T"."SAP_CODE"=:B1))
4 - filter(TO_CHAR("ISSUANCEDATE",'yyyymmdd')=:B1 AND "RN"=1)
5 - filter(ROW_NUMBER() OVER ( PARTITION BY "T_RMB"."BASECURRENCY","T_RMB"."EXCHANGECURRENCY",TO_DATE(TO_CHAR(INTERN
AL_FUNCTION("T_RMB"."ISSUANCEDATE"),'yyyymmdd'),'yyyymmdd') ORDER BY "STATUS")<=1)
8 - filter(ROWNUM=1)
9 - filter("EXCHANGECURRENCY"= (SELECT /*+ */ "T"."t_code" FROM "A2" WHERE "T"."TYPENAME"='currency' AND
"T"."SAP_CODE"=:B1))
10 - filter(TO_CHAR("ISSUANCEDATE",'yyyymmdd')=:B1 AND "RN"=1)
11 - filter(ROW_NUMBER() OVER ( PARTITION BY "T_RMB"."BASECURRENCY","T_RMB"."EXCHANGECURRENCY",TO_DATE(TO_CHAR(INTERN
AL_FUNCTION("T_RMB"."ISSUANCEDATE"),'yyyymmdd'),'yyyymmdd') ORDER BY "STATUS")<=1)看到这种不加排序的子句直接使用rownum=1的查询，本身就是对数据要求不严格。所以只要达到它的一个特性（最多返回一行）就可以，上面的标量语句就可以改为：
(select max(s.exechangerate) exechangerate,
s.exchangecurrency,
TO_CHAR(s.issuancedate, 'yyyymmdd') issuancedate1
from tcsa.uccexchange s
group by TO_CHAR(s.issuancedate, 'yyyymmdd'),s.exchangecurrency)整体改写后：
select postdate, CusNo, dramount, Debit_Sum_RMB, cramount, Credit_Sum_RMB
from (select to_date(o.postdate, 'yyyymmdd') postdate,
cur.c_code as CusNo,
o.dramount,
round(o.dramount * (s.exechangerate1 / 100), 2) AS Debit_Sum_RMB,
o.cramount,
round(o.cramount * (s.exechangerate1 / 100), 2) AS Credit_Sum_RMB,
s.exchangecurrency,
t.t_code
from tcsa.mm_sap_voucher_detail_to o
left join cur_tbl cur
on cur.c_abbr = o.currencycode
left join (select max(exechangerate) exechangerate1,
exchangecurrency,
TO_CHAR(issuancedate, 'yyyymmdd') issuancedate1
from tcsa.uccexchange
group by exchangecurrency,
TO_CHAR(issuancedate, 'yyyymmdd')) s
on s.issuancedate1 = o.postdate
left join tcsa.sap_code_mapping t
on t.typename = 'currency'
and t.sap_code = o.currencycode
where o.postdate >= '20170101'
and o.postdate < '20180101') tab
where t_code = exchangecurrency;