Help on Exists

pkelly — Thu, 23 Sep 2010 12:25:30 GMT

I have loaded a table from my live system which has a field called tmpRun which holds a run number.

I am then trying to bring in another table via SQL but only bring ion records where "veh-run" value = tmpRun value

The code I have is as follows...

TempPickOrder:

LOAD

"Ord-Type" AS OrderType,

"Ord-Num" AS OrderNumber,

"pick-num" AS PickNumber,

"veh-run" AS VehicleRun,

ourrecid AS OrigRecID;

SELECT

PUB."pck_ord"."Ord-Type",

PUB."pck_ord"."Ord-Num",

PUB."pck_ord"."pick-num",

PUB."pck_ord"."veh-run",

PUB."pck_item".ourrecid

FROM

PUB."pck_ord", PUB."pck_item"

WHERE

EXISTS("veh-run", tmpRun)

AND (PUB."pck_item"."Ord-Num" = PUB."pck_ord"."Ord-Num"

AND PUB."pck_item"."Ord-Type" = PUB."pck_ord"."Ord-Type"

AND PUB."pck_item"."pick-num" = PUB."pck_ord"."pick-num");

Keep getting a syntax error - any ideas where I am going wrong?

Help on Exists

danielrozental — Thu, 23 Sep 2010 14:04:54 GMT

Paul, when you're doing a SELECT the WHERE clause is a SQL clause run through the ODBC, you can't use qlikview functions.

You can use the exists like this

LOAD

"Ord-Type" AS OrderType,

"Ord-Num" AS OrderNumber,

"pick-num" AS PickNumber,

"veh-run" AS VehicleRun,

ourrecid AS OrigRecID

WHERE EXISTS("veh-run", tmpRun);

SELECT

PUB."pck_ord"."Ord-Type",

PUB."pck_ord"."Ord-Num",

PUB."pck_ord"."pick-num",

PUB."pck_ord"."veh-run",

PUB."pck_item".ourrecid

FROM

PUB."pck_ord", PUB."pck_item"

WHERE

(PUB."pck_item"."Ord-Num" = PUB."pck_ord"."Ord-Num"

AND PUB."pck_item"."Ord-Type" = PUB."pck_ord"."Ord-Type"

AND PUB."pck_item"."pick-num" = PUB."pck_ord"."pick-num");

But it will still cause all records to be extracted from the database not just the ones that were loaded previously.

topic Help on Exists in QlikView

Help on Exists

Help on Exists