topic SQL Select adds invalid spaces to data. in QlikView

SQL Select adds invalid spaces to data.

avantime — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have an issue, i have a simple SQL select script, extracting about 16m rows of data.

If i select only one row of data, everything is OK. If I select the entire contents of the database, I get corrupt results with missing leading zeros, spaces added randomly, etc.

Any idea why this happens?

Re: SQL Select adds invalid spaces to data.

jpenuliar — Fri, 28 Oct 2016 17:14:31 GMT

can you post your code?

Re: SQL Select adds invalid spaces to data.

Anonymous — Fri, 28 Oct 2016 20:41:03 GMT

you could add RTRIM() and LTRIM() to fields with leading and trailing spaces.

REPLACE() works well to replace characters in the string.

Re: SQL Select adds invalid spaces to data.

Sun, 30 Oct 2016 08:53:27 GMT

Where is SQL Server located, is it on same network from where you are extracting?

Does QlikView connection fails at the end?

Re: SQL Select adds invalid spaces to data.

Anil_Babu_Samineni — Sun, 30 Oct 2016 10:56:29 GMT

What do you mean that? What is the key of random numbers. Can you elaborate more

Re: SQL Select adds invalid spaces to data.

avantime — Mon, 31 Oct 2016 08:49:00 GMT

Hi, sorry for the late reply, been out of town!

The extract script is very simple, something like this:

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=xxxxxx;Data Source=xxxxxx;Extended Properties="DSN=xxxxxxx;SERVER=xxxxxx;PORT=xxxxx;DATABASE=xxxxxxx;AUTHENTICATION METHOD=0;UID=xxxxxxx;STATIC CURSORS=0;QUERY TIMEOUT=1;UNICODE SQLTYPES=0"];

SELECT

MDB.ArtNR,

MDB.ProdNR

FROM MainDatabase MDB;

The normal output should be:

ArtNR	ProdNR
02311	02311
08522	08522

Instead I get:

ArtNR	ProdNR
2311	231 1
8522	852 2

I repeat, this problem does not occur if I select just one ArtNR at a time.

The server I am querying is on the same network.

Re: SQL Select adds invalid spaces to data.

Frank_Hartmann — Mon, 31 Oct 2016 08:52:26 GMT

Have a look at Verbatim Variable:

Qlikview SET Verbatim = 1 by RFB 184 - YouTube

hope this helps

Re: SQL Select adds invalid spaces to data.

avantime — Mon, 31 Oct 2016 10:09:01 GMT

Tried it already.. Doesn`t work.

Re: SQL Select adds invalid spaces to data.

avantime — Mon, 31 Oct 2016 19:53:18 GMT

Solved it.

Changed my code into:

LOAD

Text(ArtNR),

Text(ProdNR);

SELECT

MDB.ArtNR,

MDB.ProdNR

FROM MainDatabase MDB;

All loads OK Now.