Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: Db2/Oracle-Cannot get rid off spaces!

    Management has decided to migrate from db2 Luw v9. to Oracle 11g.

    This is the DB2 query I created to dump to a delimited file a bunch of data (within a bash script).

    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;

    Data output looks nice and neat like this

    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

    This is the ORACLE query I created to do the same (within a bash script)

    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;

    Data output is a mess of leading/trailing spaces all over the pplace (added R/LTRIMS have no effect):

    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


    This is the DDL for the DB2 tables

    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


    This is the DDL for the Oracle tables

    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


    How can I fix my query in Oracle? What am I missing here?
    Last edited by kristo5747; 11-04-13 at 18:34.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    If you use "comma(s)" between the columns sql*plus will report each column data as fixed column length. Therefore to remove the spaces and avoid this, use the concatenation (||) function like this:
    Code:
    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 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;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •