0 Replies Latest reply: Dec 7, 2009 3:15 PM by Christian_Henke RSS

    loss of leading zeros when loading oracle varchar column for QVD

    Christian_Henke

      I know that this has been discussed in the past but may be the QV development team will find a solution for the future...

      The problem: I load my QVD files from Oracle automatically with a loop for all my tables and than select * like:

      T_00:
      SQLTables;
      My_tables:
      LOAD
      TABLE_NAME as My_tables
      RESIDENT T_00
      WHERE
      TABLE_SCHEM = 'My';
      DROP TABLE T_00;
      Let NoOfTables = NoOfRows('My_tables');
      For i = 1 to $(NoOfTables)
      Let My_LoadTableName = peek('My_tables', $(i)-1,'My_tables');
      SQL SELECT * FROM RCGC.$(My_LoadTableName);
      STORE $(My_LoadTableName) INTO $(My_LoadTableName).QVD;
      DROP TABLE $(My_LoadTableName);
      Next

      No way to format the ID columns to text by using text(columnname) because text() does not work with star for fieldname.

      Even more confusing is, that QV interprets by checking the first value in the original oracle column. As a result the same IDs are loaded different from two tables:

      first table ID = A001, 01234, 2345 => result in QV is ID = A001, 01234, 2345

      second table ID = 2345, 01234, A001=> result in QV is ID = 2345, 1234, A001

      Took some time to figure out... - but if someone wants to force a text field it may be an idea to have a definite non-numeric in rownum 1in the first line of the oracle table (also a sort in the SQL might work but not with the star example).

      Any creative ideas out there?