How to pass values from one loaded table as sql parameters to another

[jmclaren]

Hi. I'm trying to read data from two tables that share a common field, 'PartNo'. Both tables are massive and the only records I want to load are ones created today (usually not more than 100 records). The first table has a 'Date_created' field, so that's easy, but how do I load only the fields from the second table that match the first? If I try and load all the component records it takes too long. The script i've tried below gives the error:

"[Informix] A subquery has returned not exactly one row.
Components:
load Parent as PartNo, Component, Usage"

I just don't think my sql select on the second table is the right way to do it...

Note: The component_data table might have no matching parts or several.

---- Script ----
LET vToday = Date(Today(),'DD/MM/YYYY');

Parts:
load PartNo, Description, Date_created;
sql select * from part_data where Date_created = '$(vToday)';

Components:
load Parent as PartNo, Component, Usage;
sql select * from component_data
where Parent = (select distinct PartNo from part_data where Date_created = '$(vToday)')

The 'Parts' load returns 33 records, and I would expect the 'Components' load (when working) would return about 50-60 records.

[marcus_sommer · Accepted solution]

You could try the following approach:

Parts:
load PartNo, Description, Date_created;
sql select * from part_data where Date_created = '$(vToday)';

FilterList: load distinct concat(PartNo, ',') as FilterList resident Parts:

let vFilterList = peek('FilterList', 0, 'FilterList');

Components:
load Parent as PartNo, Component, Usage;
sql select * from component_data
where Parent IN($(vFilterList))

If PartNo isn't numeric and/or your db requires any kind of quotes you need to add them during the concat() aggregation.

- Marcus

[jmclaren]

Thank you so much Marcus - that's going to be extremely useful to know that technique. As you had suggested, the only change I had to make was to change my concat statement :

from:

concat(PartNo, ',')

to:

concat(Chr(39)&PartNo&Chr(39), ',')

Jim