Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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