Use field to select from database

[marcusbohman]

I am selecting PART_ID from a cross reference table with a where clause on PART_NUMBER as below.

Parts:

LOAD*;

SQL SELECT "PART_ID"

FROM "DATABASE1" where PART_NUMBER IN ('84171490','84017929','9519027','9998266');

I would like to use the PART_IDs in another where clause similar to below.

SQL SELECT "PART_ID",

    "INFO1",

    "INFO2"

FROM "DATABASE2" where IN (Parts.PART_ID);

I have tried a LEFT JOIN but DATABASE2 is to big for me to select everything and then join it.

/Marcus

[olivierrobin · Accepted solution]

hello

why don't you try a single sql select like  (syntax may not be correct depending on your base):

SQL SELECT "PART_ID",

    "INFO1",

    "INFO2"

FROM "DATABASE2" join "DATABASE1"

on "DATABASE2"."PART_ID"="DATABASE1"."PART_ID"

and  PART_NUMBER IN ('84171490','84017929','9519027','9998266');

so that the database is working and QV gets only the rows you need

[Peter_Cammaert]

You can combine the two Selects in a SQL JOIN so that the limited set of rows from table1 is joined with only relevant details from Table2.

A QlikView alternative consists of first storing the list of relevant PART_ID values in a variable and then use that variable in each WHERE clause. For example, something like this (off the top of my head, some tuning may be required):

Parts:

SQL SELECT "PART_ID"

FROM "DATABASE1" where PART_NUMBER IN ('84171490','84017929','9519027','9998266');

PartsList:

LOAD Concat(chr(39) & PART_ID & chr(39), ', ') AS PartsList RESIDENT Parts;

LET vPartsList = peek('PartsList');

DROP Table PartsList;

SQL SELECT "PART_ID",

    "INFO1",

    "INFO2"

FROM "DATABASE2" WHERE PART_ID IN ($(vPartsList));

Best,

Peter

[marcusbohman]

Thank you Olivier,

Works like a charm!