topic Re: SQL parse error, problem with Exist() in QlikView

SQL parse error, problem with Exist()

Tue, 02 Dec 2014 08:24:57 GMT

Hi, in script i load two tables,

ACTIVITY_DEFS:

LOAD DESCRIPTIVENAME as DESCRIPTIVENAME_ACT_DEFS,

ID as ID_ACT_DEFS,

INSTRUCTIVENAME as INSTRUCTIVENAME_ACT_DEFS;

SQL SELECT DESCRIPTIVENAME,

ID,

INSTRUCTIVENAME

FROM "VT_DM_DATABASE"."BI_TWFLACTIVITYDEFS";

ACTIVITY_INSTANCES:

LOAD ACTIVITY_ID as ACTIVITY_ID_ACT_INST,

ID as ID_ACT_INST,

"IPROCESS_ID" as ID_PROCESS_INSTANCES,

"LASTASSIGNEDBY_ID" as LASTASSIGNEDBY_ID_ACT_INST,

"LASTWORKER_ID" as LASTWORKER_ID_ACT_INST,

STARTDATE as NA_STAN_DATA_ACT_INST,

SQL SELECT ACTIVITY_ID,

ID,

"IPROCESS_ID",

"LASTASSIGNEDBY_ID",

"LASTWORKER_ID",

STARTDATE

FROM "VT_DM_DATABASE"."BI_TWFLACTIVITYINSTANCES" WHERE EXISTS (ID_ACT_DEFS, ACTIVITY_ID);

And after adding this WHERE EXISTS ... statement i got the following error :

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: parse error.

SQL SELECT ACTIVITY_ID,

ID,

"IPROCESS_ID",

"LASTASSIGNEDBY_ID",

"LASTWORKER_ID",

STARTDATE

FROM "VT_DM_DATABASE"."BI_TWFLACTIVITYINSTANCES" WHERE EXISTS (ID_ACTIVITY_DEFS, ACTIVITY_ID)

I tried almost everything to solve this, what am i missing ?

Re: SQL parse error, problem with Exist()

Peter_Cammaert — Tue, 02 Dec 2014 08:28:32 GMT

EXISTS() is a QlikView function, not a SQL function. You cannot use QlikView functions in SQL statements.

Add the WHERE EXISTS() clause to the PReceding LOAD instead, and you're good.

Best,

Peter

Re: SQL parse error, problem with Exist()

mato32188 — Tue, 02 Dec 2014 08:28:40 GMT

Hi Jakub,

in where exists clause use also SELECT ... FROM syntax.

Re: SQL parse error, problem with Exist()

anbu1984 — Fri, 05 Dec 2014 10:30:53 GMT

SQL SELECT ACTIVITY_ID,

ID,

"IPROCESS_ID",

"LASTASSIGNEDBY_ID",

"LASTWORKER_ID",

STARTDATE

FROM "VT_DM_DATABASE"."BI_TWFLACTIVITYINSTANCES" Where ID_ACT_DEFS Not In (Select ID from "VT_DM_DATABASE"."BI_TWFLACTIVITYDEFS");

ACTIVITY_INSTANCES:

LOAD ACTIVITY_ID as ACTIVITY_ID_ACT_INST,

ID as ID_ACT_INST,

"IPROCESS_ID" as ID_PROCESS_INSTANCES,

"LASTASSIGNEDBY_ID" as LASTASSIGNEDBY_ID_ACT_INST,

"LASTWORKER_ID" as LASTWORKER_ID_ACT_INST,

STARTDATE as NA_STAN_DATA_ACT_INST WHERE EXISTS (ID_ACT_DEFS, ACTIVITY_ID);

SQL SELECT ACTIVITY_ID,

ID,

"IPROCESS_ID",

"LASTASSIGNEDBY_ID",

"LASTWORKER_ID",

STARTDATE

FROM "VT_DM_DATABASE"."BI_TWFLACTIVITYINSTANCES" ;

Re: SQL parse error, problem with Exist()

Fri, 05 Dec 2014 10:49:51 GMT

I think you mean "ID_ACT_DEFS In" and not "ID_ACT_DEFS Not In"

Re: SQL parse error, problem with Exist()

rajeshvaswani77 — Fri, 05 Dec 2014 10:56:43 GMT

Hi,

Exists will need a sub query.

You seem to be mixing the QlikView exists here.

thanks,

Rajesh Vaswani

Re: SQL parse error, problem with Exist()

anbu1984 — Fri, 05 Dec 2014 10:58:49 GMT

Yes you are right