How to pass values from one loaded table as sql parameters to another
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.