Help on Exists

[pkelly]

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?

[danielrozental · Accepted solution]

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.