-1

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.

Improve this question
5
  • 1
    which Sybase RDBMS product (ASE? IQ? SQLAnywhere? Advantage?) and version? Commented Oct 21, 2023 at 18:33
  • fwiw, the accepted answer for your previous question had 3 different answers; you've chosen the 'simpler' non-ANSI compliant query so a) performance probably won't be great and b) will be harder to understand/maintain for someone who's expecting something that's more along the lines of being ANSI compliant Commented Oct 21, 2023 at 18:35
  • 1
    what would the expected output be if you had 4 matching rows all with NOMINAL_VALUE = 100? there would 4 different combinations of rows that have a sum of 300 ... ??? Commented Oct 21, 2023 at 18:44
  • Hallo @markp-fuso, the sybase RDBMS is Adaptive Server Enterprise/16.0. If there are 4 matching rows all with NOMINAL_VALUE = 100, I would like to have one combination that fulfills the criteria. It doesn't matter which one. It could, e.g., be rows 1, 2 and 3 ... or 2, 3, 4 or 1, 3, 4 or ... Commented Oct 21, 2023 at 19:11
  • @markp-fuso: is a window-function really the solution here? How would I write such a window-function in Adaptive Server Enterprise/16.0? Commented Oct 21, 2023 at 19:13

4 Answers 4

Reset to default
1

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:

  • write a query with an assumed max number of self-joins (gets quite unwieldly for anything beyond 3-5 self-joins; see FriendlyDragon's comment about 1K * 1K = 1M variants)
  • write a batch process (or stored proc) that uses a looping construct to perform (tail) recursive joins (plus some means of deciding when to 'quit' recursing)

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Sorry for answer instead of comments, but I want to put some tables here... maybe this answer make sence to delete after discussion...

Ok, you want to know, how you can agregate amount, and your example is very clear with one extra line.

id SECURITIES_ID DEBITCREDIT NOMINAL_VALUE
2 B D 100
4 B D 200
6 B D 1

But, let's try imaging, which row will be extra in this example?

id SECURITIES_ID DEBITCREDIT NOMINAL_VALUE
2 B D 100,00
4 B D 100,00
6 B D 100,00
9 B D 100,00

As result, looks like you have wrong goal - you are trying to divide one whole number to separate smaller, but you don't know how many can be these separate parts...

For some reason it will be enought to some window function? But this is not very applicable for WHERE/HAVING part, am I right?

Improve this answer

2 Comments

Hallo @FriendlyDragon, thank you for your answer. In your example, I would like to select one combination that fulfils my criteria - it doesn't matter, which one. It could, e.g., be ids 2, 4 and 6 ... or 4, 6, 9 or 2, 6, 9 or ...
Ok, let's imagin that we have 1000 rows... It is possible, that your result can be reached by 1 row, by 2 rows, by 3 rows, ... by 1000 rows, if each of them have amount 0.30... so, you need to calculate each of result for 1000 rows, so you should calculate 1000 * 1000 = 1 000 000 variants at least... I am still beleave, that your goal is wrong...
1

This shold be done using a window function, in a subquery

select RECORD_ID,
       BOOKING_ID FROM 
 (
select RECORD_ID,
       BOOKING_ID,
  SUM(NOMINAL_VALUE) OVER(ORDER BY RECORD_ID) sumes
from   RECORDS
where  SECURITIES_ID = 'B'
and    DEBITCREDIT   = 'D') CTE WHERE sumes >= 300
RECORD_ID BOOKING_ID
4 40
6 60
Improve this answer

1 Comment

Hallo @nbk, Thank you for your answer. When I run your select statement, however, I get the following syntax errors: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'OVER'.
1

Use a recursive query to get all possible row combinations (1, 2, 3, 4, 5, 6, 1-2, 1-3, 1-4, 1-5, 1-6, 2-3, 2-4, 2-5, 2-6, 3-4, 3-5, 3-6, 4-5, 4-6, 5-6, 1-2-3, 1-2-4, ...). Well, you can stop adding rows of course once you hit a total of 300.

I haven't found any online tool for trying Sybase queries, so here is one for SQL Server that may need some little adjustments. I am collecting combinations here, remembering the participating IDs in a string starting an ending with a comma, e.g. ',1,3,5,'. Thus I can later look for an ID in the string, e.g. looking for ID 5 with where ',1,3,5,' LIKE '%,5,%'.

with 
  candidates as
  (
    select *
    from records
    where securities_id = 'B'
    and debitcredit = 'D'
  ),
  series (total, ids, last_id) as
  (
    select
      nominal_value,
      cast(',' + cast(record_id as varchar(20)) + ',' as varchar(1000)),
      record_id
    from candidates
    union all
    select 
      s.total + c.nominal_value,
      cast(s.ids + cast(c.record_id as varchar(20)) + ',' as varchar(1000)),
      c.record_id
    from series s
    join candidates c on c.record_id > s.last_id
    where s.total + c.nominal_value <= 300
  ),
  one_match as
  (
    select top 1 *
    from series
    where total = 300
  )
select *
from records r
where exists
(
  select null
  from one_match m
  where m.ids like '%,' + cast(r.record_id as varchar(20)) + ',%'
)
order by record_id;

Demo: https://dbfiddle.uk/ScRGartF

Here is another demo, where I select all matches: https://dbfiddle.uk/jdxIsGep

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.