0

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?

James Z
  • 12,104
  • 10
  • 27
  • 43
Chammu
  • 83
  • 1
  • 10

1 Answers1

0

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

Paul Vernon
  • 3,487
  • 1
  • 9
  • 21
  • 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