Try CREATE TABLE AS SELECT command  
Its easy to use. See: CREATE TABLE AS SELECT (SQL) | InterSystems SQL Reference | InterSystems IRIS Data Platform 2025.2

Hey Steve, you said 'no subqueries,' but I figured Common Table Expressions with window functions were fair game, right? 😉
This is just a shot based on your example data. Not entirely sure I nailed your explanation about 'previous' and 'next'? 🤔

Maybe this will help:

WITH RankedEntries AS 
(
    SELECT 
        TheNO,
        TheID,
        TheDate,
        Item,
        ROW_NUMBER() OVER (PARTITION BY TheID, Item ORDER BY TheDate ASC) AS ItemRank
    FROM MultipleEntries
)
SELECT 
    TheNO,
    TheID,
    TheDate,
    Item,
    CASE 
        WHEN ItemRank = 1 THEN 'new'
        ELSE 'old'
    END AS Status
FROM RankedEntries
ORDER BY TheNO;

Andreas

That looks strange. If you execute the query via JDBC the error ist the same. A CAST to the same datatype as provided from Config.ConfigFile works:

SELECT
       CAST(ID AS VARCHAR(512)),
       CAST(CPFName AS VARCHAR(255)),
       CAST(Comments AS VARCHAR(4096)),
       CAST(Name AS VARCHAR(64)),
       CAST(Product AS VARCHAR(64)),
       CAST(SectionHeader AS VARCHAR(255)),
       CAST(Version AS VARCHAR(7))
FROM Config.ConfigFile

Hi! Unfortunately there is no way to UPDATE the %DESCRIPTION information on a table or column with SQL.
But you can add these information while CREATEing a table like this:

or this way for a column:

Please see CREATE TABLE | InterSystems SQL Reference | InterSystems IRIS Data Platform 2022.1 for more details.

By default the %DESCRIPTION information is projected to the Remarks column via JDBC:

As you can see the text comes from the %DESCRIPTION property:

This works also for the columns.
This is from JDBC:

This is from INFORMATION_SCHEMA.COLUMNS:

Andreas

I tried it with a small example:

I could not find any problem. Generally this variant works from my point of view.
Exceute a "PURGE CACHED QUERIES" and retry, maybe this will help.
Andreas
 

Maybe you haven't send the complete statement?
This works for me via JDBC, tested with Caché2018.1.0.184  and IRIS 2021.2.0.617

LOCK TABLE dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK TABLE dc_data_flights.airports IN SHARE MODE

-- OR
LOCK dc_data_flights.airports IN SHARE MODE

If try this I get the same error like you:

-- this create an error, the "IN EXCLUSIVE MODE" or "IN SHARE MODE" is missing
LOCK TABLE dc_data_flights.airports

Andreas