topic QV 64 vs. QB 32 bit - problems in SQL Selec in QlikView

QV 64 vs. QB 32 bit - problems in SQL Selec

johnlauridsen — Wed, 05 Aug 2009 20:56:31 GMT

We are using 32 bit at the moment but planning to switch to 64 bit. at the test before switching I noticed that some of the documents wasn't able to reload. Even that they work on 32 bit.

The script is the following:

ODBC

CONNECT TO [SG1;DBQ=LIO_1] (XUserId is Xxxxxxxxx, XPassword is 99999999999);

SQL SELECT *

FROM

LIORDER."LAGER_ST" where "LAG_MANDANT"='KO' and LAG_ART_TYP=0;

I get the following error:

SQL Error:[Oracle][ODBC][Ora]ORA-01406: hentet kolonneværdi blev afskåret

SQL Scriptline:
SQL State:S1000
SQL SELECT *
FROM LIORDER."LAGER_ST" where "LAG_MANDANT"='KO' and LAG_ART_TYP=0

If I leave out the "where...." I have no problems. In the 32 bit it working fine.

Anyone having some suggestions?

John

QV 64 vs. QB 32 bit - problems in SQL Selec

Thu, 06 Aug 2009 19:17:33 GMT

I think the problem comes from your ODBC drivers :

1) Please check that your OBDC driver is 64 bit compliant.
2) If you need to use a 32 bit connection in a 64 bit environment, check that post :

http://community.qlik.com/forums/p/12436/49208.aspx#49208

the point has already been discussed in the forums, search : "ODBC 64 bits" related posts

Hope it helps

Regards

QV 64 vs. QB 32 bit - problems in SQL Selec

johnlauridsen — Tue, 11 Aug 2009 15:35:26 GMT

Thank you

I'm not sure it has to do with the 64 bit .. or partly at least.

Next part in the script I have the same From: "

FROM

LIORDER.V_LAGER_ST_ALPHA where /*"LAG_BEARB" = 0 and*/ LAG_ART_TYP = 3;

And here it just passes fine through... In the first part I directly used SQL select... and in the second part I use a Load... and then SQL select.

QV 64 vs. QB 32 bit - problems in SQL Selec

Bjorn_Wedbratt — Tue, 11 Aug 2009 16:08:18 GMT

ORA-01406:	fetched column value was truncated
Cause:	In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3.

Try and see if you can find the offending field(s) in your table and note the data type and size (for example if it is data type with large numeric values). When you found the field that's being truncated you could try converting it using CHAR, like:

TO_CHAR(field) field

(in the SQL-statement). See if this will give any result.

QV 64 vs. QB 32 bit - problems in SQL Selec

johnlauridsen — Tue, 11 Aug 2009 16:49:32 GMT

Thank you. I cannot get the scripts right.

Load
"LAG_MANDANT",
"LAG_ART_TYP",
"LAG_ART_NUM",
"LAG_BEARB",
"LAG_SFORM",
"LAG_VERFUEGBAR",
"LAG_EINK_PREIS",
"LAG_ZUSCHN_JN",
"LAG_GRP_ID";
SQL SELECT
"LAG_MANDANT",
"LAG_ART_TYP",
"LAG_ART_NUM",
"LAG_BEARB",
"LAG_SFORM",
"LAG_VERFUEGBAR",
"LAG_EINK_PREIS",
"LAG_ZUSCHN_JN",
"LAG_GRP_ID"
FROM LIORDER.V_LAGER_ST_ALPHA where LAG_BEARB = 0 and LAG_ART_TYP = 0;

If I exchange the LAG_ART_TYP = 0 with a 1 instead I get no problems (but not having the right criteria 🙂 )

QV 64 vs. QB 32 bit - problems in SQL Selec

Bjorn_Wedbratt — Tue, 11 Aug 2009 18:49:22 GMT

Hi,

Try removing one field at the time from the SQL-statement until you find which field(s) are causing the problem. When you found it (I guess it's a numeric field) try:

SQL SELECT
"LAG_MANDANT",
"LAG_ART_TYP",
TO_CHAR( LAG_ART_NUM) LAG_ART_NUM,
"LAG_BEARB",
"LAG_SFORM",
"LAG_VERFUEGBAR",
"LAG_EINK_PREIS",
"LAG_ZUSCHN_JN",
"LAG_GRP_ID"
FROM LIORDER.V_LAGER_ST_ALPHA ;

Here I assume the field causing the problem is the LAG_ART_NUM field.

QV 64 vs. QB 32 bit - problems in SQL Selec

johnlauridsen — Tue, 11 Aug 2009 19:03:34 GMT

Okay.. I have solved the probem. I copied from another application a part of the script including similar text and added the fields I needed.

I have not use TO_CHAR but what I have now works.

Thank you for your assistance.. '

/John

QV 64 vs. QB 32 bit - problems in SQL Selec

johnlauridsen — Thu, 13 Aug 2009 19:00:16 GMT

Sorry... I have to come bakc to this thread.

I have a script. really close the above.

SQL SELECT *

FROM LIORDER."LAGER_ST" where /*"LAG_ZUSCHN_JN" = 0 and*/ "LAG_ART_TYP"= 0 and "LAG_MANDANT"='ES';

If I have the last ='ES'; it works... If I change it to ='KO' its not working... but if I put it ='ko' it works but without any records as the records are in capital letters.

QV 64 vs. QB 32 bit - problems in SQL Selec

Bjorn_Wedbratt — Thu, 13 Aug 2009 21:09:41 GMT

Hi John,

Are you getting the same error as before (SQL Error:[Oracle][ODBC][Ora]ORA-01406: hentet kolonneværdi blev afskåret)? If yes, it's because one or many fieldvalues are truncated, as described in the post I copied earlier. The reason why you don't get the error if you for example limit the result using LAG_MANDANDT='ES' is simply because then you're limiting the number of records, filtering out the values being truncated.

If you get this error when putting LAG_MANDANT='KO' then you know that somewhere in those records there are values being truncated. Again, try to limit the number of fields in the SELECT-statement, instead of using * until you find the field causing the problem, and try converting it using TO_CHAR().

QV 64 vs. QB 32 bit - problems in SQL Selec

Tue, 15 Feb 2011 01:22:39 GMT

Database is Oracle 9.2.0.6.0 in AIX Version 5.3
AMERICAN _ AMERICA. ZHS16GBK

generate qvd computer is Windows Server 2003R2 Enterprise X64 Edition
Oracle Client is 10g
AMERICAN _ AMERICA. ZHS16GBK

the number of fields using to_char() is ok,
have other methods???