Only non-numeric rows from varchar2-column are displayed

[schmimla21]

Hello,

I created a script in which I load several columns from different tables via OLE-DB Connect.  Now my problem is, that there is one varchar2-column, which contains numeric values as well as alphanumeric values.  This column is  loaded this way:

SQL SELECT XYZ as "nameXYZ" FROM SCHEMA.TABLE STORE TABLE into \\...\file.qvd (qvd);

A listbox of the Field  "nameXYZ" only shows the alphanumeric values of the column. For example:

10 records should be displayed, 2 alphanumeric values and 8 numeric values. In the listbox there are  now 10 rows highlighted (= white), but only the two alphanumeric values are displayed, ant the other 8 values seem to be "Null"

I hope that the description of my problem is understandable.

I `d appreciate any hint, thanks in advance for your effort..

[marcus_sommer]

NULL couldn't be displayed within the listbox therefore there are values available but they seem to lack a string-representation. Quite often will be a transformation within a preceeding load solve such task. Therefore try:

load text(XYZ) as nameXYZ;

 SQL SELECT XYZ FROM SCHEMA.TABLE

- Marcus

[jonathandienst]

>> In the listbox there are  now 10 rows highlighted (= white), but only the two alphanumeric values are displayed, 

This may be a silly question, but check that the 'white' values are not the numbers right justified in the list box. As Marcus says, NULL values would not be displayed in a list box.

[schmimla21]

Thanks for the replys. I noticed that when I copied the white rows from clipboard to an editor, that also the numeric values were present in the editor...so a "lack of a string-presentation" sounds quite accurate.

I found a way, to get rid of the problem: Right-click on the listbox -> "properties" -> "presentation" -> "alignment" -> "text and numbers" -> "right"

and "multiline rows" -> set checkbox "wrap text" with a height of one row.

So the "preceeding load" did not solve the problem.  So there is a solution now, but I wonder if there is a possibilty to solve the problem without manual manipulation in the listox-settings right in the script...

[marcus_sommer · Accepted solution]

This means there was no issue with loading the data else just with discovering them within the listbox which seems just caused from your used settings there. Per default are numerical value right-aligned and strings left-aligned and AFAIK there is no way to change it. What you could do is to adjust the aligning within the properties and use a copy & paste approach to duplicate the listbox and then change the used field or to transfer the object-settings (is the same logic like in MS office - but not all properties could be transferred) from one object to another.

- Marcus