OpenEdge实现类SQL Cross Apply：关联两表取另一表首行数据

Hey there! Let's tackle this problem of getting the first matching row from la_ofart for each row in la_of—essentially replicating SQL's CROSS APPLY in OpenEdge. Since you mentioned FETCH FIRST is only available in OpenEdge 11+, I'll break this down by version to cover all bases:

OpenEdge 11 and Later (Supports FETCH FIRST & APPLY) #

If you're on OpenEdge 11 or newer, you can use CROSS APPLY directly (it's supported in this version!) combined with FETCH FIRST 1 ROW ONLY to get exactly the first matching row from la_ofart. This is the closest equivalent to standard SQL's CROSS APPLY:

SELECT 
    o.*, 
    art.*
FROM PUB.la_of o
CROSS APPLY (
    SELECT * 
    FROM PUB.la_ofart art
    WHERE art.empr_cod = o.empr_cod
      AND art.Cod_Ordf = o.Cod_Ordf
      AND art.Num_ordex = o.Num_ordex
      AND art.Num_partida = o.Num_partida
    -- Add an ORDER BY here if you need the "first" row sorted by a specific field, e.g.:
    -- ORDER BY art.Fecha_creacion ASC
    FETCH FIRST 1 ROW ONLY
) art;

If you prefer to avoid APPLY, you can also use a correlated subquery with FETCH FIRST to pull individual columns from la_ofart:

SELECT 
    o.*,
    (SELECT art.Col1 FROM PUB.la_ofart art WHERE art.empr_cod = o.empr_cod AND ... FETCH FIRST 1 ROW ONLY) AS ArtCol1,
    (SELECT art.Col2 FROM PUB.la_ofart art WHERE art.empr_cod = o.empr_cod AND ... FETCH FIRST 1 ROW ONLY) AS ArtCol2
    -- Repeat for each column you need from la_ofart
FROM PUB.la_of o;

OpenEdge Versions Before 11 (No FETCH FIRST) #

For older versions, you'll need to use a subquery to identify the unique first row per la_of record. You can use either a primary key column (if la_ofart has one) or ROWID (a unique identifier for every Progress record) to pick the first match:

Using a Primary Key #

If la_ofart has a unique primary key (e.g., Id_Art), use MIN() to get the smallest key for each matching set:

SELECT 
    o.*, 
    art.*
FROM PUB.la_of o
JOIN PUB.la_ofart art ON 
    art.empr_cod = o.empr_cod
    AND art.Cod_Ordf = o.Cod_Ordf
    AND art.Num_ordex = o.Num_ordex
    AND art.Num_partida = o.Num_partida
    AND art.Id_Art = (
        SELECT MIN(Id_Art)
        FROM PUB.la_ofart art_sub
        WHERE art_sub.empr_cod = o.empr_cod
          AND art_sub.Cod_Ordf = o.Cod_Ordf
          AND art_sub.Num_ordex = o.Num_ordex
          AND art_sub.Num_partida = o.Num_partida
    );

Using ROWID (No Primary Key Needed) #

If there's no primary key, ROWID works since it's unique for every record in the database:

SELECT 
    o.*, 
    art.*
FROM PUB.la_of o
JOIN PUB.la_ofart art ON 
    art.empr_cod = o.empr_cod
    AND art.Cod_Ordf = o.Cod_Ordf
    AND art.Num_ordex = o.Num_ordex
    AND art.Num_partida = o.Num_partida
    AND art.ROWID = (
        SELECT MIN(ROWID)
        FROM PUB.la_ofart art_sub
        WHERE art_sub.empr_cod = o.empr_cod
          AND art_sub.Cod_Ordf = o.Cod_Ordf
          AND art_sub.Num_ordex = o.Num_ordex
          AND art_sub.Num_partida = o.Num_partida
    );

A quick heads-up: If you need the "first" row sorted by a specific field (like a creation date), the OpenEdge 11+ approach is cleaner since you can add an ORDER BY in the APPLY subquery. For older versions, MIN(ROWID) will grab the earliest inserted record by default—if you need sorted results, you might need to use window functions if your version supports them.

内容的提问来源于stack exchange，提问作者Ricardo Castro