Extracting from DB datasource will incorrectly format fields.

[#1]

Currently I am extracting the Item Master table via SQL from our Oracle DB.  The PN field when extracted is being stored incorrectly.  An example would be that we have PNs 00001, 01 and 1.0000.  In the source tables they all show that way.  When I extract from QlikView, it is storing all 3 PNs as 00001.  This is happening with several "like" PNs and isn't following any logic. I could understand that if QLik thought the Part Number field was a number it would read every field as "1".  But it's like QlikView is doing a data cleanse and assuming that these all should be 00001.  Has anyone seen this before? This doesn't seem to be limited to only one data source. I have seen this extracting from the Oracle OBIEE warehouse, Oracle EBS DB and Microsoft SQL server.

Thank
Jason

SQL Results via SQL Editor

QVD results

[mato32188]

Hi Jason,

use preceding load

Load ROWWID, text(PARTNUM), PRODNAME

;

SQL SELECT...

       FROM

Martin

[#3]

Martin,

This is the script that I tried.  I received an error that appeared to not take.

Thanks

Jason

LOAD

rowwid, text(partnum),prodname;

select
row_wid,part_num,prod_name

from w_product_d where row_wid IN ('49095492','49193572','49055983');

[Anonymous · Accepted solution]

On the second line put an underscore in row_wid

LOAD

row_wid, text(partnum),prodname;

select
row_wid,part_num,prod_name

from w_product_d where row_wid IN ('49095492','49193572','49055983');

[#5]

Bill,

Thanks, I tried that too and got the same result where it couldn't find the field "row_wid".

LOAD

row_wid, text(partnum),prodname;
select
row_wid,part_num,prod_name
from w_product_d where row_wid IN ('49095492','49193572','49055983');

Thanks

Jason

[JonnyPoole]

Jason did the QV wizard create this SQL for you or is it a copy/paste of SQL ?

If you haven't already tried it, use the ODBC connection in the script to select this table and one field (row_wid) to see how Qlik autogenerates the SQL and see if syntax differences exist.

[maxgro]

pay attention to case with oracle

load

     ROW_WID,

     ...

sql select

     row_wid,

     ....  

example

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Password=xxxxxxxxxxx;Persist Security Info=True;User ID=xxxxxxx;Data Source=xxxxx;Extended Properties=""];

load text(FIELD1) as FIELD1;

select field1 from a;

[#8]

Massimo,

That was it. The capitalization was incorrect and causing the issues.

Thanks

Jason

[#9]

http://dbmanagement.info/Tutorials/Oracle_EBS.htm

Oracle EBS- Video Training Tutorial DVDs (2 DVDs)

More than 8 GB of Video files Sale Price 19.97