将子查询替换为连接：Informix 10.00查询改写求助

Got it, let's work through this rewrite for your Informix 10.00 query. Older Informix versions have strict limitations on subqueries in the WHERE clause—especially when they include aggregate functions like MIN()—so switching to a join approach is the right move here.

First, let's recap the logic of your original query:

You want distinct dco and datpos values from the bkhis table where dco is greater than or equal to the smallest dco value from records where dag is yesterday (TODAY - 1) and dco doesn't exceed dag.

Here's how to convert this to a join-based query that works in Informix 10.00:

SELECT DISTINCT b.dco, b.datpos
FROM bkhis b, (
    SELECT MIN(dco) AS min_dco
    FROM bkhis
    WHERE dag = TODAY - 1 AND dco <= dag
) m
WHERE b.dco >= m.min_dco

How this works: #

• The subquery (wrapped in parentheses and aliased as m) calculates the minimum dco value exactly like your original subquery does. Informix 10.00 allows subqueries in the FROM clause (unlike some restricted subquery uses in WHERE), so this is safe.
• We use an implicit cross join between the main bkhis table (aliased as b) and the single-row result from m. Since m only returns one value, this doesn't duplicate rows unnecessarily.
• The WHERE clause filters the main table rows to only those where dco meets or exceeds the precomputed min_dco value.
• DISTINCT keeps the result set identical to your original query by removing duplicate dco/datpos pairs.

If you prefer explicit join syntax (which is more readable for some), you can use CROSS JOIN instead of the comma-separated tables—Informix 10.00 supports this too:

SELECT DISTINCT b.dco, b.datpos
FROM bkhis b
CROSS JOIN (
    SELECT MIN(dco) AS min_dco
    FROM bkhis
    WHERE dag = TODAY - 1 AND dco <= dag
) m
WHERE b.dco >= m.min_dco

Both versions will behave exactly like your original query: if there are no records matching dag = TODAY - 1 AND dco <= dag, the subquery m will return NULL, and the WHERE clause will filter out all rows (since dco >= NULL evaluates to false), just like your original query would.

内容的提问来源于stack exchange，提问作者souhaib fares