topic Specific fields causing 1% of rows to not load in a SQL Select QVD load in QlikView

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Thu, 21 May 2009 23:01:52 GMT

In the Sql select load statement below, if I uncomment "PART_NO,
800+ fewer rows out of 180,000 are found. Does anyone else have this sort of problem where
certain fields are causing a few rows to not load?
Does anyone have any suggestions for troubleshooting or correcting incomplete SQL loads?

OrderLines:

LOAD

//"PART_NO" AS OrderLines_ItemNbr,

"MCODE" AS OrderLines_MfgCode,

"ORDER_NO" AS OrderLines_OrderNbr,

"LINE" AS OrderLines_OrderLineNbr

SQL SELECT

//"PART_NO",

"MCODE",

"ORDER_NO",

"LINE"

FROM "ORDER_LINE";

STORE OrderLines INTO [UV_OrderLines.qvd];

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Thu, 21 May 2009 23:22:10 GMT

Is the PART_NO Field null in some rows ?

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Anonymous — Thu, 21 May 2009 23:31:47 GMT

Assuming that the "LINE" field is the unique record identifier (key), I don't see how that commented line can make a difference. What bothers me is the absence of ";" before "SQL SELECT"... Hope it's a mistyping here.
Wonderining how you define the number of rows in the result. What happens if you ran the same query directly in the database, with and without PART_NO? Waht changes if you add DISTINCT?

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Thu, 21 May 2009 23:33:51 GMT

Only 35 rows are null.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Anonymous — Thu, 21 May 2009 23:40:39 GMT

I doubt that nulls should make any difference in this field, unless thay are in the key field - that must not be null by definition anyway.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Thu, 21 May 2009 23:48:06 GMT

I just edited the script for this forum. ( The semicolon is there)
The rows have a unique id and my dba says he is seeing the higher count with the part_no field.
I can see the missing rows and part_no when I go into the app.
I will ask my dba to run the query with and without DISTINCT directly in the database.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Anonymous — Thu, 21 May 2009 23:54:24 GMT

Well, the "I can see the missing rows and part_no when I go into the app" means that nothing is missing(?) Maybe you have all the data after all, just some rows are identical.
Let's see if the DISTINCT changes anything.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Anonymous — Fri, 22 May 2009 08:10:51 GMT

One more thing to check - do you have a load statemant in the script that has suffix INNER, and contains the field PART_NO? If yes - see online help about INNER.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

rbecher — Fri, 22 May 2009 20:36:08 GMT

Is ORDER_LINE a table or a view?

If it's a view with a join I could imaging different results depending on which columns are used.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Fri, 22 May 2009 21:21:45 GMT

I don't have an INNER suffix.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Fri, 22 May 2009 21:23:32 GMT

I don't know, but I will check.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Fri, 22 May 2009 21:38:22 GMT

It's a table, but the table is from a multivalue database from IBM called Universe.
The ODBC connector splits some of the actual tables into virtual tables to normalize
so SQL scripts can be used.

Specific fields causing 1% of rows to not load in a SQL Select QVD load

rbecher — Fri, 22 May 2009 22:43:57 GMT

So, then I would double check this behavior with a different tool via ODBC.

Is this an online database with write operations? Maybe it's an issue with read/write consistency (transaction level).

Specific fields causing 1% of rows to not load in a SQL Select QVD load

Fri, 22 May 2009 22:50:06 GMT

Good point. I will do this and let you know the results.