topic Where statement in odbc load in QlikView

Where statement in odbc load

Mon, 08 Jul 2013 08:12:55 GMT

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');

Re: Where statement in odbc load

Gysbert_Wassenaar — Mon, 08 Jul 2013 08:20:51 GMT

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.

Re: Where statement in odbc load

hic — Mon, 08 Jul 2013 08:22:17 GMT

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

Re: Where statement in odbc load

sushil353 — Mon, 08 Jul 2013 08:23:07 GMT

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

Re: Where statement in odbc load

Sokkorn — Mon, 08 Jul 2013 08:28:02 GMT

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