sql - Db2/Oracle-无法摆脱空格

Question

管理层已决定从 db2 Luw v9 迁移。到 Oracle 11g。

这是我创建的 DB2 查询，用于将一堆数据(在 bash 脚本中)转储到一个带分隔符的文件中。

EXPORT TO /tmp/dod.v3.del OF DEL MODIFIED BY NOCHARDEL COLDEL| DATESISO MESSAGES /tmp/dod.v3.msg SELECT DISTINCT A.CAMID, A.POSTTIME, A.MODDATE,A.RID,A.SOFTWAREVERSION,B.IPINFO,B.NUMBE ROFRESETSSINCELASTSWDL,C.TOTALNUMOFCOMPLETEDDLS,C. TOTALNUMBEROFDOWNLOADSSTARTED,C.DAILYDOWNLOADSIZE, C.DAILYDOWNLOADTIME,CONCAT(A.MODELNUMBER,(CONCAT('-',A.MANUFACTURERID))) FROM DMS.STBHEADER A JOIN DMS.STBSTATUS B ON A.DMS_ID=B.DMS_ID AND A.MODDATE = CURRENT_DATE - 1 DAY AND A.CAMID NOT IN (-11,125,117) LEFT JOIN DMS.BBVODSTATS C ON A.DMS_ID=C.DMS_ID FOR FETCH ONLY WITH UR;

数据输出看起来漂亮整洁

2925917748|2003-12-05-17.26.58.000000|2013-11-03|36816426252|740||0|||||H25-500
3030176931|2004-11-07-11.48.52.000000|2013-11-03|25167455119|797||15|0|4|0|0|HR44-700
3026464853|2004-11-07-12.40.54.000000|2013-11-03|25166398575|797||17|0|3|0|0|HR44-500

这是我创建的 ORACLE 查询(在 bash 脚本中)

SET ARRAYSIZE 5000 FEEDBACK OFF ECHO OFF HEADING OFF LIN 999 MAXD 999 NEWP NONE;
SET RECSEP OFF TERM OFF TRIM ON TRIMSPOOL ON TRUNCATE OFF UND OFF VER OFF WRA OFF;
SET COLSEP |;
COLUMN CAMID FORMAT 9999999999999;
COLUMN RID FORMAT 9999999999999;
spool /tmp/dod.v3.del.ora;
SELECT DISTINCT
A.CAMID,
LTRIM(RTRIM(TO_CHAR(A.POSTTIME,'YYYY-MM-DD-HH24.MI.SS.FF6'),' ') ,' '),
LTRIM(RTRIM(TO_CHAR(A.MODDATE,'YYYY-MM-DD') , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(A.RID), ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(A.SOFTWAREVERSION), ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(B.IPINFO), ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(B.NUMBEROFRESETSSINCELASTSWDL) , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(C.TOTALNUMOFCOMPLETEDDLS) , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(C.TOTALNUMBEROFDOWNLOADSSTARTE D) , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(C.DAILYDOWNLOADSIZE) , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(C.DAILYDOWNLOADTIME) , ' ') ,' '),
LTRIM(RTRIM(TO_CHAR(CONCAT(A.MODELNUMBER,(CONCAT('-',A.MANUFACTURERID))) ), ' ') ,' ')
FROM
DMS.STBHEADER A
JOIN
DMS.STBSTATUS B
ON A.DMS_ID=B.DMS_ID
AND
A.CAMID NOT IN (-11,125,117)
LEFT JOIN
DMS.BBVODSTATS C
ON A.DMS_ID=C.DMS_ID;

数据输出是整个 pplace 的前导/尾随空格(添加的 R/LTRIMS 无效):

1814861090|2013-08-27-08.05.17.000000 |2013-08-30|28883762982 |736 |null |0 |4 |4 |0 |0 |HR21-700
2072851526|2013-08-27-08.05.29.000000 |2013-08-30|33064562144 |736 |null |1 |42 |42 |0 |0 |HR24-500

这是 DB2 表的 DDL

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CAMID                           SYSIBM    BIGINT                       8     0 No
RID                             SYSIBM    BIGINT                       8     0 No
SOFTWAREVERSION                 SYSIBM    VARCHAR                     25     0 Yes
MODELNUMBER                     SYSIBM    VARCHAR                     25     0 Yes
MANUFACTURERID                  SYSIBM    VARCHAR                     25     0 Yes
POSTTIME                        SYSIBM    TIMESTAMP                   10     6 Yes
MODDATE                         SYSIBM    DATE                         4     0 No
NUMBEROFRESETSSINCELASTSWDL     SYSIBM    INTEGER                      4     0 Yes
IPINFO                          SYSIBM    VARCHAR                    100     0 Yes
TOTALNUMOFCOMPLETEDDLS          SYSIBM    BIGINT                       8     0 Yes
TOTALNUMBEROFDOWNLOADSSTARTED   SYSIBM    BIGINT                       8     0 Yes
DAILYDOWNLOADSIZE               SYSIBM    BIGINT                       8     0 Yes
DAILYDOWNLOADTIME               SYSIBM    BIGINT                       8     0 Yes

这是 Oracle 表的 DDL

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CAMID                                     NOT NULL NUMBER
 RID                                       NOT NULL NUMBER
 SOFTWAREVERSION                                    VARCHAR2(25)
 MODELNUMBER                                        VARCHAR2(25)
 MANUFACTURERID                                     VARCHAR2(25)
 POSTTIME                                           TIMESTAMP(6)
 MODDATE                                   NOT NULL TIMESTAMP(0)
 NUMBEROFRESETSSINCELASTSWDL                        NUMBER
 IPINFO                                             VARCHAR2(100)
 TOTALNUMOFCOMPLETEDDLS                             NUMBER
 TOTALNUMBEROFDOWNLOADSSTARTED                      NUMBER
 DAILYDOWNLOADSIZE                                  NUMBER
 DAILYDOWNLOADTIME                                  NUMBER

我在这里缺少什么？如何在 Oracle 中修复我的查询？

Answer 1

输出仍然在列中，并且 Oracle(或者更确切地说，SQL*Plus)不会删除每行值中的空格，因此即使您 TRIM值不同长度的字符串列，所有行将以相同的宽度显示该列。它还增加了列宽以允许隐藏的标题，这让事情变得更糟。例如，第二列的派生标题 - 如果显示 - 将是 LTRIM(RTRIM(TO_CHAR(A.POSTTIME,'YYYY-MM-DD-HH24.MI.SS.FF6'),''),'') ，因此即使所有值都比该值短，该列也会显示为 67 个字符宽。您可以通过使用较短名称为列设置别名来部分阻止这种情况，但最终仍会为空值留下空格。

我发现真正剥离所有内容的唯一方法是手动将列与分隔符连接起来，这会产生一列输出:

SELECT DISTINCT
A.CAMID
  ||'|'|| TO_CHAR(A.POSTTIME,'YYYY-MM-DD-HH24.MI.SS.FF6')
  ||'|'|| TO_CHAR(A.MODDATE,'YYYY-MM-DD')
  ||'|'|| A.RID
  ||'|'|| A.SOFTWAREVERSION
  ||'|'|| B.IPINFO
  ||'|'|| B.NUMBEROFRESETSSINCELASTSWDL
  ||'|'|| C.TOTALNUMOFCOMPLETEDDLS
  ||'|'|| C.TOTALNUMBEROFDOWNLOADSSTARTED
  ||'|'|| C.DAILYDOWNLOADSIZE
  ||'|'|| C.DAILYDOWNLOADTIME
  ||'|'|| A.MODELNUMBER ||'-'|| A.MANUFACTURERID
FROM
STBHEADER A
JOIN
STBSTATUS B
ON A.DMS_ID=B.DMS_ID
AND
A.CAMID NOT IN (-11,125,117)
LEFT JOIN
BBVODSTATS C
ON A.DMS_ID=C.DMS_ID;

3030176931|2004-11-07-11.48.52.000000|2013-11-03|25167455119|797||15|0|4|0|0|HR44-700
3026464853|2004-11-07-12.40.54.000000|2013-11-03|25166398575|797||17|0|4|0|0|HR44-500
2925917748|2003-12-05-17.26.58.000000|2013-11-03|36816426252|740||0|||||H25-500

SQL Fiddle .

这不需要修剪值，但如果您需要这样做，那么使用 TRIM 会更简单。而不是单独的 LTRIM和 RTRIM调用，默认是删除空格 - 所以不是 LTRIM(RTRIM(value, ' '), ' ')你可以做 TRIM(value) . (但明确无害)。我也遗漏了 TO_CHAR调用数值来简化事情，但你可能更喜欢明确设置它们 - 在这种情况下注意 FM格式修饰符。

您的一些 SET命令， MAXD , NEWP和 TRUNCATE , are obsolete ，并且不再需要其他一些 - COLSEP特别是，因为现在输出中只有一列。

如果你真的明白这个词 null在输出中，你有 SET NULL null某处，可能在登录脚本中；这也不会出现在这个串联版本中，因为它应用于输出列(同样，只有一个，并且不能为空)而不是单个基础表列。