SQL parse error, problem with Exist()

[#1]

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 ?

[Peter_Cammaert · Accepted solution]

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

[mato32188]

Hi Jakub,

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

BR

M

[anbu1984]

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

Or

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" ;

[#5]

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

[rajeshvaswani77]

Hi,

Exists will need a sub query.

You seem to be mixing the QlikView exists here.

thanks,

Rajesh Vaswani

[anbu1984]

Yes you are right