Where statement in odbc load

[#1]

Hi,

I'm trying to use a where clause in my script for loading through ODBC, so I can restrict the size of the QVD files that I'll later load.

My where clause works in the transformation step from QVD to QVW, but it does not work in the script that connects to ODBC.

Here is a similar question that wasn't answered yet:

http://community.qlik.com/message/290620

Any ideas? I'm trying to avoid a workaround with preceding load because that will make the script unnecessarily complex.

Kr,

Daniel

Forgot my script:

JOBS:

//FIRST 10000

SQL SELECT

"A_BEHAVIOUR" AS "JOB0_A_BEHAVIOUR",

"A_CHILDJOBS" AS "JOB0_A_CHILDJOBS",

"A_CHILDORDER" AS "JOB0_A_CHILDORDER",

DATEADD(MILLISECOND, "A_CREATIONTIMETS" % 1000, DATEADD(SECOND, "A_CREATIONTIMETS" / 1000, '19700101')) AS "JOB0_A_CREATIONTIMETS_DATE"

FROM DBxxxxx.dbo."JCS_JOB0" Where not (Len(trim(purgechar(A_BEHAVIOUR, chr(160))))=0 and JOB0_F_JOBCHAINSTEP>0 and JOB0_A_BEHAVIOUR = '2');

[Gysbert_Wassenaar · Accepted solution]

You can't use Qlikview functions in an SQL Select statement. The sql statement is executed by the database server, not Qlikview. So you need to replace (Len(trim(purgechar(A_BEHAVIOUR, chr(160))))=0 with something that's valid sql for your database. Ask your local friendly database administrator if you need help with that.

[hic]

Well, your where clause will only work in a Load, e.g in a preceding load. It will not work in a SELECT. The SELECT statement is not evaluated by QlikVIew. It is evaluated by the ODBC driver,

HIC

[sushil353]

Hi,

you are trying to use qlikview functions in your SQL query.. that is why you are getting issues in where clause..

try to load this table without where clause and use resident load and then put the where condition having qlikview functions.

HTH

Sushil

[Sokkorn]

Hi Daniel,

Purgechar() work in QlikView and I don't think it work in ODBC. Let try this query

Where not (Len(Trim(Replace(A,Chr(160),'')))=0 and JOB0_F_JOBCHAINSTEP>0 and JOB0_A_BEHAVIOUR = '2');

Regards,

Sokkorn