根据你之前的问题，我想你可能是想对XMLAGG()中的元素进行排序，所以你会想把你的order by语句in the xmlagg call：

-- excerpt from your main query...
-- Transaction Attributes
(Select xmlagg(xmlconcat(xmlelement(evalname(trdattr.TRANSACTION_ATTRIBUTE_CODE), 1))
               order by 
               case TRANSACTION_ATTRIBUTE_CODE 
               when 'tns:SW'       then 1
               when 'tns:EE'       THEN 2
               when 'tns:TP'       THEN 3
               when 'tns:TT_WNT'   then 4
               when 'tns:TT_D'     THEN 5
               when 'tns:MR_T'     THEN 6
               when 'tns:MR_UZ'    THEN 7
               when 'tns:I_42'     THEN 8
               when 'tns:I_63'     THEN 9
               when 'tns:B_SPV'    THEN 10
               when 'tns:B_SPV_DOSTAWA' THEN 11
               when 'tns:B_MPV_PROWIZJA' THEN 12
               when 'tns:MPP' THEN 13
               ELSE 14 END)
 From    ( Select distinct xx_info_v12 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table
           Where  xx_info_V1 = 'AR'
           and    xx_info_v12 <> 'tns:'
           and    xx_info_n3 = ar_trx.trx_id 
           UNION ALL
           Select xx_info_v20 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table 
           Where  xx_info_V1 = 'AR'
           and    xx_info_n3 = ar_trx.trx_id 
           and    xx_info_v20 <> 0) trdattr),
-- Kboxes
-- continue main query

字符串
我没法测试，但也许可以试试。

你的独立查询可以工作，但是在你的主查询中，你把它用作scalar subquery expression。因此，主查询的简化版本是：

select (
-- start of your standlone query
Select xmlagg(xmlconcat(xmlelement(evalname(trdattr.TRANSACTION_ATTRIBUTE_CODE), 1)))
From    ( Select distinct xx_info_v12 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table
           Where  xx_info_V1 = 'AR'
           and    xx_info_v12 <> 'tns:'
           --and      xx_info_n3 = ar_trx.trx_id 
           UNION ALL
           Select xx_info_v20 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table 
           Where  xx_info_V1 = 'AR'
           --and      xx_info_n3 = ar_trx.trx_id 
           and    xx_info_v20 <> 0) trdattr
order by 
case TRANSACTION_ATTRIBUTE_CODE 
when 'tns:SW'       then 1
when 'tns:EE'       THEN 2
when 'tns:TP'       THEN 3
when 'tns:TT_WNT'   then 4
when 'tns:TT_D'     THEN 5
when 'tns:MR_T'     THEN 6
when 'tns:MR_UZ'    THEN 7
when 'tns:I_42'     THEN 8
when 'tns:I_63'     THEN 9
when 'tns:B_SPV'    THEN 10
when 'tns:B_SPV_DOSTAWA' THEN 11
when 'tns:B_MPV_PROWIZJA' THEN 12
when 'tns:MPP' THEN 13
ELSE 14 END
-- end of your standalone query
)
from dual

字符串
.也抛出“ORA-00907：missing right parenthesis”。
正如docs所说：
标量子查询表达式是只从一行返回一个列值的子查询。
这就是你在这里所看到的--一行一列，这就是你的XMLAgg结果。尝试对这一行进行排序是有点无意义的，也是没有意义的。Oracle并不期望在这一点上看到一个order-by子句，它期望看到子查询表达式的右括号--因此它抛出了错误。
当您将order-by移动到内联视图时，它可能更有意义：

Select xmlagg(xmlconcat(xmlelement(evalname(trdattr.TRANSACTION_ATTRIBUTE_CODE), 1)))
From    ( Select distinct xx_info_v12 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table
           Where  xx_info_V1 = 'AR'
           and    xx_info_v12 <> 'tns:'
           --and      xx_info_n3 = ar_trx.trx_id 
           UNION ALL
           Select xx_info_v20 TRANSACTION_ATTRIBUTE_CODE
           From   xxsample_table 
           Where  xx_info_V1 = 'AR'
           --and      xx_info_n3 = ar_trx.trx_id 
           and    xx_info_v20 <> 0
           order by 
           case TRANSACTION_ATTRIBUTE_CODE 
           when 'tns:SW'       then 1
           when 'tns:EE'       THEN 2
           when 'tns:TP'       THEN 3
           when 'tns:TT_WNT'   then 4
           when 'tns:TT_D'     THEN 5
           when 'tns:MR_T'     THEN 6
           when 'tns:MR_UZ'    THEN 7
           when 'tns:I_42'     THEN 8
           when 'tns:I_63'     THEN 9
           when 'tns:B_SPV'    THEN 10
           when 'tns:B_SPV_DOSTAWA' THEN 11
           when 'tns:B_MPV_PROWIZJA' THEN 12
           when 'tns:MPP' THEN 13
           ELSE 14 END
        ) trdattr

型
你会得到“ORA-01785：ORDER BY item must be the number of a SELECT-list expression”，这可能是this bug。这里的变通方法也可以工作，添加另一个级别的子查询：

Select xmlagg(xmlconcat(xmlelement(evalname(trdattr.TRANSACTION_ATTRIBUTE_CODE), 1)))
From (
  Select *
  From    ( Select distinct xx_info_v12 TRANSACTION_ATTRIBUTE_CODE
             From   xxsample_table
             Where  xx_info_V1 = 'AR'
             and    xx_info_v12 <> 'tns:'
             --and      xx_info_n3 = ar_trx.trx_id 
             UNION ALL
             Select xx_info_v20 TRANSACTION_ATTRIBUTE_CODE
             From   xxsample_table 
             Where  xx_info_V1 = 'AR'
             --and      xx_info_n3 = ar_trx.trx_id 
             and    xx_info_v20 <> 0
          )
         order by 
         case TRANSACTION_ATTRIBUTE_CODE 
         when 'tns:SW'       then 1
         when 'tns:EE'       THEN 2
         when 'tns:TP'       THEN 3
         when 'tns:TT_WNT'   then 4
         when 'tns:TT_D'     THEN 5
         when 'tns:MR_T'     THEN 6
         when 'tns:MR_UZ'    THEN 7
         when 'tns:I_42'     THEN 8
         when 'tns:I_63'     THEN 9
         when 'tns:B_SPV'    THEN 10
         when 'tns:B_SPV_DOSTAWA' THEN 11
         when 'tns:B_MPV_PROWIZJA' THEN 12
         when 'tns:MPP' THEN 13
         ELSE 14 END
  ) trdattr

型
然后，您可以将 that 作为标量子查询表达式。

下面是另一种可能有用的方法：

-- SQL*Plus formatting commands
SET PAGESIZE 60
SET LINESIZE 120
COLUMN sort_key         NOPRINT
COLUMN tablespace_name  FORMAT A18
COLUMN file_name        FORMAT A40

-- two queries, each contributing to unified view of tablespaces and files
-- each using DECODE() just because it's less wordy (and still supported)
-- column aliases not required on expressions in second query in UNION, but I like using them
SELECT
DECODE(ts.tablespace_name,
'SYSTEM',   'A1',
'SYSAUX',   'A2',
'UNDOTBS1', 'B',
'USERS',    'D',
'Z') AS sort_key,
ts.tablespace_name,
f.file_name,
(f.bytes / 1024 / 1024) AS mbytes,
ts.status
FROM
dba_tablespaces ts JOIN dba_data_files f ON ts.tablespace_name = f.tablespace_name
UNION
SELECT
DECODE(ts.tablespace_name,
'TEMP',  'C',
'Z') AS sort_key,
ts.tablespace_name,
f.file_name,
(f.bytes / 1024 / 1024) AS mbytes,
ts.status
FROM
dba_tablespaces ts JOIN dba_temp_files f ON ts.tablespace_name = f.tablespace_name
ORDER BY
sort_key,
tablespace_name
;

-- results - in the order I prefer to see
TABLESPACE_NAME    FILE_NAME                                    MBYTES STATUS
------------------ ---------------------------------------- ---------- ---------
SYSTEM             C:\DEV\ORADATA\DB01\SYSTEM01.DBF               890 ONLINE
SYSAUX             C:\DEV\ORADATA\DB01\SYSAUX01.DBF               510 ONLINE
UNDOTBS1           C:\DEV\ORADATA\DB01\UNDOTBS01.DBF               60 ONLINE
TEMP               C:\DEV\ORADATA\DB01\TEMP01.DBF                  32 ONLINE
USERS              C:\DEV\ORADATA\DB01\USERS01.DBF                  5 ONLINE
MYDAT              C:\DEV\ORACLE19C\DATABASE\MYDAT01.DBF           800 ONLINE
MYIDX              C:\DEV\ORACLE19C\DATABASE\MYIDX01.DBF           800 ONLINE

字符串