sql server - 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?

question from:https://stackoverflow.com/questions/65883969/convert-cross-apply-in-db2-database

Answer 1

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

https://docs.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