Using SELECT TOP 1 in subquery in outer SELECT list (SAP ASE 16)

Question

I have a query that works in Sybase SQL Anywhere 11 that I need to compile in SAP Adaptive Server Enterprise 16. The code doesn't want to compile in ASE though, stating that

SELECT TOP is not allowed in a subquery.

Here's the query:

SELECT  a.value,
        Bcode = (SELECT TOP 1 b.code
                FROM    Btable b
                WHERE   b.value = a.value
                AND     b.deleted_flag = 'N'
                ORDER BY b.start_date DESC, b.start_time DESC),
        Bdesc = CASE
                    WHEN Bcode = 'a' THEN 'Alpha'
                    WHEN Bcode = 'b' THEN 'Beta'
                    WHEN Bcode = 'c' THEN 'Cappa'
                    WHEN Bcode = 'd' THEN 'Delta'
                    ELSE 'Epsilon'
                END,
        a.category,
        a.status,
        a.start_date,
        a.start_time
FROM    Atable a
WHERE   a.deleted_flag = 'N'
AND     a.start_date BETWEEN @a_datefrom AND @a_dateto
AND     a.end_date IS NULL
ORDER BY a.start_date`

What can I do to get the same result? Would the data need to be selected into a temp table and then manipulated via a set of atomic statements? Thanks in advance for your time and effort.

Answer 1

There's a Native SQL feature in SAP which you can use to execute your statement, however it must be adapted according to the link.

If you want to use SAP SQL I would do something like the following:

FORM load using p_date1 type datum p_date2 type datum.
    DATA: it_atable type standard table of ty_atable,
          it_btable type standard table of ty_btable,
          wa_btable like line of it_btable
          wa_atable like line of it_atable,
          lv_index type sy-tabix.

    SELECT  value
            category
            status
            start_date
            start_time
            end_date " Include end_date to delete nulls later
    FROM    atable
    INTO CORRESPONDING FIELDS OF TABLE it_atable
    WHERE   deleted_flag = 'N'
    AND     start_date between lv_date1 and lv_date2.

    CHECK SY-SUBRC EQ 0. " No data? Do not continue.

    SELECT value code " I wouldn't get the TOP n. Just take the data and order it
    FROM    btable
    INTO CORRESPONDING FIELDS OF TABLE it_btable
    FOR ALL ENTRIES IN it_atable
    WHERE   value = it_atable-value
            deleted_flag = 'N'
    ORDER BY start_date DESC start_time DESC.

    LOOP AT it_atable INTO wa_atable.
        lv_index = sy-tabix.

        CHECK wa_atable-end_date is not initial.
* Get the first row         
        READ TABLE it_btable INTO wa_btable WITH KEY value = wa_atable. 

        IF SY-SUBRC IS INITIAL.
            CASE wa_btable-code.
                WHEN 'a'.
                    wa_atable-bdesc = 'Alpha'.
                WHEN 'b'.
                    wa_atable-bdesc = 'Beta'.
                WHEN 'c'.
                    wa_atable-bdesc = 'Cappa'.
                WHEN 'd'.
                    wa_atable-bdesc = 'Delta'.
                OTHERS
                    wa_atable-bdesc = 'Epsilon'.
            ENDCASE.
            wa_atable-bcode = wa_btable-code.
        ENDIF.

        MODIFY it_atable FROM wa_atable INDEX lv_index.
    ENDLOOP.

    DELETE it_atable WHERE end_date IS INITIAL. " Delete the null dates
ENDFORM.

I didn't test the code because I'm not connected to SAP right now.

Finally, there's no "TOP n" statement in SAP SQL. You must use "UP TO n ROWS". Check this link.

Hope it helps.

Answer 2

I think what you need is to use HAVING.

Bcode = (SELECT MIN(code)
        FROM    Btable 
        WHERE   value = a.value
        AND     deleted_flag = 'N'
        HAVING  start_date = MIN(start_date)
        AND     start_time = MIN(start_time)
        AND     deleted_flag = 'N'
        ),

repeating the deleted_flag = 'N' test in the HAVING clause might not be necessary.

You probably need that MIN(code) because there might be more than one row in the correlated join to Btable for each row in Atable that fulfill the "has minimum date and time", and assuming they have different codes you must MIN() them.

I suspect this might still not work in that you have distinct date and time fields and the record with minimum date might not have the minimum time. I don't know what their types are but if they are a DATE and TIME, then I think you'll need to convert them to strings, concatenate, and convert back to DATETIME, all in a single expression, and run the HAVING condition on that. Otherwise change to using a DATETIME.