Convert CROSS APPLY in DB2 database

Question

I am trying to convert the below MSSQL query into DB2 query. But i am facing issues . I got to know "CROSS APPLY" doesnt exist for DB2

SQL Server query:

SELECT DISTINCT p.ID,
p.COMPANY,
p.NAME,
format(d.startTime, 'yyyy-MM-dd HH:mm:ss.fff')
FROM PROCESS p 
CROSS APPLY (SELECT MAX(END_TIME) AS startTime FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS)) AS d
WHERE p.ID = (SELECT MAX(ID) FROM PROCESS)

Error:

Error: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: APPLY;N process CROSS;JOIN

How the above query can be converted into DB2 query format?

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — SMor, Jan 25 '21 at 12:54

score 0 · Answer 1 · answered Jan 25 '21 at 14:42

The SQL Server manual says that a CROSS APPLY is used with table functions.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15#using-apply

That the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function.

Your example does not use any, so I assume it is simply the equivalent to a CROSS JOIN in Db2.

By the way, this statement would likely get the same result (assuming COMPANY and NAME are the same for a given ID)

SELECT
    ID
,   COMPANY
,   NAME
,   format(END_TIME, 'yyyy-MM-dd HH:mm:ss.fff')
FROM
(   SELECT *
    ,   ROW_NUMBER() OVER(ORDER BY ID DESC, END_TIME DESC) AS RN
    FROM
        PROCESS p 
)
WHERE
    RN = 1

This might or might not be more optimal at execution time

The important word in that quote is "can", meaning it is possible to use a table valued function. It is not a requirement. So no, a cross apply is not simply the equivalent of a cross join. I think of cross apply (or outer apply) as a way of introducing some procedural programming into the normally set based SQL language. I don't know if it is rigidly applied, but the APPLY clause is applied on a per row basis. I think the optimizer treats the apply clause independently from the rest of the query, which can be useful for complex queries. — dbenham, Sep 17 '21 at 20:45

Wes H · Answer 2 · 2023-01-11T15:35:17.920

The DB2 equivalent is the terribly named TABLE operator. The name makes it very challenging to find documentation.

See if this works for your query.

SELECT DISTINCT p.ID,
    p.COMPANY,
    p.NAME
FROM PROCESS p
JOIN TABLE (
        SELECT ID
            ,MAX(END_TIME) AS startTime
        FROM PROCESS
        WHERE ID = (
                SELECT MAX(ID)
                FROM PROCESS
                )
        GROUP BY ID
        ) AS D
ON P.ID = D.ID

However, I've found CTEs in DB2 to be very effective. In SQL Server, a CTE is almost like a layer applied to a query, similar to a view. The expressions in the CTE are generally combined with the underlying statements and executed as a single statement.

I'm not a DB2 expert, but it seems to me that a CTE is materialized to an internal table and the result is combined with the remaining statements.

With the same query, data and indexes, DB2 and SQL Server can have very different performance when CTEs are involved.

Convert CROSS APPLY in DB2 database

2 Answers2

Linked