aggregate-functionssap-asesybase-ase15

sybase sql: choose records for which sum of values in column XYZ equals some specific value - applied to subsets

Using Sybase SQL (DBMS: Adaptive Server Enterprise/16.0.), I want to find the records fulffilling certain simple requirements (such as DEBITCREDIT == 'D') AND the following requirement: the sum of the nominal values of these records must equal 300.

Here is a screenshot as an example:

Example

In the screenhot the purpose of my select statement would be to select the RECORD_IDs highlighted in yellow, namely RECORD_ID 2 and RECORD_ID 4. (Please note that NOMINAL_VALUE of RECORD_ID 2 + NOMINAL_VALUE of RECORD_ID 4 = 300.)

The following code would achieve this purpose, if it wasn't for RECORD 6:

create table RECORDS
(RECORD_ID      int
,SECURITIES_ID  char(1)
,DEBITCREDIT    char(1)
,NOMINAL_VALUE  int
,BOOKING_ID     int
)
go

insert RECORDS values (1,'A','C',100,10)
insert RECORDS values (2,'B','D',100,20)
insert RECORDS values (3,'B','C',100,30)
insert RECORDS values (4,'B','D',200,40)  
insert RECORDS values (5,'C','D', 50,50)
insert RECORDS values (6,'B','D',  1,60)
go


select RECORD_ID,
       BOOKING_ID
from   RECORDS
where  SECURITIES_ID = 'B'
and    DEBITCREDIT   = 'D'
group by SECURITIES_ID, 
         DEBITCREDIT
having sum(NOMINAL_VALUE) = 300
order by 1,2

go

Because of RECORD 6, sum(NOMINAL_VALUE) of all entries with (SECURITIES_ID = 'B' and DEBITCREDIT = 'D') is 301 (rather than 300).

How can I select the entries, for which all of the following is true (even if these conditions are true for a subset only): SECURITIES_ID = 'B', DEBITCREDIT = 'D' and sum(NOMINAL_VALUE) = 300

P.S.: this question is a follow-up question to the folowing question: sybase sql: choose records for which sum of values in column XYZ equals some specific value

P.P.S.: If there are several combinations available, which fulfill the criteria, then the query should simply choose one of those. Which one, doesn't matter. ... Here is an example: example_2

In this example, the query should select either Records 2 and 4 or Records 4 and 6. Which combination (2/4 or 4/6) is selected, doesn't matter.

Solution

  • NOTE: while Sybase SQLAnywhere and Sybase IQ have a more robust SQL dialect (eg, CTEs, windows functions), Sybase ASE's SQL dialect is rather old/outdated/limited (thanks to SAP effectively killing off ASE development over the last 13 years)

    Without support for (recursive) CTEs (eg, see Thorsten's answer) we're left with a couple options:

    One idea based on an assumption that we'll find the desired sum within a max of 3 self-joins:

    declare @total int
select  @total = 300

select  r1.RECORD_ID,
        r2.RECORD_ID,
        r3.RECORD_ID

from    RECORDS r1

left
join    RECORDS r2
on      r1.RECORD_ID     < r2.RECORD_ID
and     r1.SECURITIES_ID = r2.SECURITIES_ID
and     r1.DEBITCREDIT   = r2.DEBITCREDIT
and     @total           > r1.NOMINAL_VALUE

left
join    RECORDS r3
on      r2.RECORD_ID     < r3.RECORD_ID
and     r2.SECURITIES_ID = r3.SECURITIES_ID
and     r2.DEBITCREDIT   = r3.DEBITCREDIT
and     @total           > (r1.NOMINAL_VALUE + r2.NOMINAL_VALUE)

where   r1.SECURITIES_ID = 'B'
and     r1.DEBITCREDIT   = 'D'

and     r1.NOMINAL_VALUE + isnull(r2.NOMINAL_VALUE,0) + isnull(r3.NOMINAL_VALUE,0) = @total

    NOTE: to limit output to one row either a) add top 1 to the query or b) run set rowcount 1 before running the query (and set rowcount 0 after the query)

    Using OP's most recent updated table data:

    insert RECORDS values (1,'A','C',100,10)
insert RECORDS values (2,'B','D',100,20)
insert RECORDS values (3,'B','C',100,30)
insert RECORDS values (4,'B','D',200,40)
insert RECORDS values (5,'C','D', 50,50)
insert RECORDS values (6,'B','D',100,60)

    For @total = 100:

     RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2        NULL        NULL
           6        NULL        NULL

    For @total = 200:

     RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           6        NULL
           4        NULL        NULL

    For @total = 300:

     RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           4        NULL
           4           6        NULL

    For @total = 400:

     RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
           2           4           6

    For @total = 700:

     RECORD_ID   RECORD_ID   RECORD_ID
 ----------- ----------- -----------
 -- no results --

    NOTE: results generated with ASE 16.0 SP04 PL04