Author Topic: SQL running total  (Read 3953 times)

Per E. Johannessen

  • Full Member
  • ***
  • Posts: 244
  • Karma: +3/-0
    • View Profile
SQL running total
« on: May 19, 2023, 05:54:52 pm »
Hi,

This statement works with SQLite;

select amount, (select sum(t2.amount) from example t2 where t2.id <= t1.id) as accumulated from example t1 order by id

Does anyone know how to make it work with dBase (IV/V)?

(I've found many examples for other databases but none of them works with dBase.)

Doug Clark

  • Sr. Member
  • ****
  • Posts: 305
  • Karma: +7/-1
    • View Profile
Re: SQL running total
« Reply #1 on: May 25, 2023, 07:40:59 pm »
Yes - with an Intersolv ODBC dbase driver  (l2dbf12.dll date 10/18/1999)

The example below shows that sql statement, rewritten to  work with some
test data that I have.  rtotal = your accumulated (the running total), district = your amount, and
the table name is voters instead of your example.  I also added a
WHERE last_name='CLARK'  clause because my test data has 31k rows.  And it is
SLOW because of what it is doing.  You have a SELECT statement where
you have replaced a column name with another SELECT statement - so it is
executing a SELECT statement for each row that it retrieves from the first
SELECT statement.  BTW this is covered on page 274 of Joe Celko's SQL for Smarties book.

It is worth noting that the Borland Database Engine does not support this statement.

It is also worth noting that while this works, it probably doesn't really get you
what you want.  The column ID in your SQL (voter_id in mine) is a unique value.
It is unlikely the values in this column sort the data the way you want to view it,
and you must order on the column(s) in the <= part of the WHERE.

So the second image shows a variation of your and my first SQL statement.  Instead of
voter_id (or in your case ID), I am using last_name and first_name concatenated together.
CONCAT() is the scalar function that concatenates stuff together: the {fn }  part is
an ODBC escape clause that Intersolv wants around scalar functions.
Yes the Intersolv dBase ODBC driver supports the CONCAT() function as long as it is escaped.

The problem with this is more than one row in the data has the same first and last name.

What I really want to do is concatenate together last_name,first_name and voter_id, because
voter_id is unique for all rows in the data. But
I cannot do that because voter_id is a numeric column and you cannot concatenate together
two dissimilar data types.  And the Intersolv driver does not have a CAST() function for
changing the data type.

So yes the Intersolv driver will allow you to issue the SQL statement you have written and
it will get the correct data back.  But if you want to order the data differently you will
have to find a unique column, or combination of columns that uniquely identify each row, that
you can live with sorting by.

As for how you use the Intersolv driver.  The application I have attached screen shots of works
with Intersolv drivers - even evaluation drivers.  However it is months away from being released.
If you have a strong need to do this now, and are willing to live with half baked software that
will probably require you to reboot with a different LIBPATH statement while you are using it,
I will send you the app - send me a message or email.  Martin knows how to get ahold of me.


Per E. Johannessen

  • Full Member
  • ***
  • Posts: 244
  • Karma: +3/-0
    • View Profile
Re: SQL running total
« Reply #2 on: May 27, 2023, 06:51:41 pm »
Thanks for info, Doug.
I tried your SQL-statements using the drivers that are bundled with DBExpert, (from 1994) and it will not work.
Error message: "Mismatched parens". Have tried a number of variations with "(" and ")" but no success yet.
I will try some "evaluation" drivers as soon as I get the chance.
Nice to hear that your're working on a "database application". Right now I don't have the need to implement running totals through SQL.
since the Rexx script provided by Lars, in another post, works fine. 
Anyway, I'm very interested your application and will be happy to test when it is ready for that.




Per E. Johannessen

  • Full Member
  • ***
  • Posts: 244
  • Karma: +3/-0
    • View Profile
Re: SQL running total
« Reply #3 on: February 05, 2024, 07:47:25 pm »
Hi, Doug, sent you a pm.

Per E. Johannessen

  • Full Member
  • ***
  • Posts: 244
  • Karma: +3/-0
    • View Profile
Re: SQL running total
« Reply #4 on: February 18, 2024, 06:51:40 pm »
Some more testing ....

select a.rowid, a.accno, a.bkgdate, a.amount, (select sum(b.amount) from finance b where b.rowid <= a.rowid and a.accno = b.accno group by b.accno) from finance a order by a.accno



However, I also need "order by BKGDATE" but that will change the running total column and make it useless.

Have an idea about running an update query with the necessary "group by" and "order by" and that it inserts record number,
incremented by 1. in a new column.
Ideas anoyone?
 

roberto

  • Hero Member
  • *****
  • Posts: 809
  • Karma: +3/-6
    • View Profile
Re: SQL running total
« Reply #5 on: February 19, 2024, 11:58:04 am »
Have an idea about running an update query with the necessary "group by" and "order by" and that it inserts record number,
incremented by 1. in a new column.
Ideas anoyone?
I'm not sure I understand you, but if you are using Dbexpert, you can do this:
In the query add a call to macro in a new colunm, as
Code: [Select]
AST:DBERUNMACRO('MNUMERAR',ASIEN)
and a new macro like this:
Code: [Select]
/*MNUMERAR*/
NUMERIC DIGITS(9)
ARG N

GUARDAR='OS2ENVIRONMENT'
ASUNO=VALUE(ASUNO,ASUNO,GUARDAR)
ASDOS=VALUE('ASUNO',ASUNO+1,GUARDAR)
/*
SAY '***' ASUNO 'ASUNO' VALUE(ASUNO) 'OTRO' VALUE('ASUNO')
SAY '***' ASDOS 'ASDOS' VALUE(ASDOS) 'OTRO' VALUE('ASDOS')
*/
/*ESTE A¥ADIDO ES PARA RECOGER EL PRIMER VALOR DEL PRIMER ASIENTO*/
IF VALUE(ASUNO)=0 | VALUE(ASUNO)=SPACE(1) THEN
DO
ASUNO=VALUE(ASUNO,N,GUARDAR)
ASDOS=VALUE('ASUNO',N+1,GUARDAR)
RETURN N
EXIT
END

saludos

Per E. Johannessen

  • Full Member
  • ***
  • Posts: 244
  • Karma: +3/-0
    • View Profile
Re: SQL running total
« Reply #6 on: February 19, 2024, 08:00:28 pm »
Thanks Roberto, but unfortunately it returns no values.
The query I mentioned earlier is run from VX-Rexx and Intersolv 2.11, can't make it work with DBExpert.
I'm trying to create a column that holds the sequence numbers for the records, like the image below:


Doug Clark

  • Sr. Member
  • ****
  • Posts: 305
  • Karma: +7/-1
    • View Profile
Re: SQL running total
« Reply #7 on: February 23, 2024, 08:00:13 pm »
Per,

Try the the ODBC drivers attached.  You need both files. 

Unzip ODBCINIFILES.ZIP in C:\OS2

Unzip ODBCFILES.ZIP in C:\

These will work with you Approach and other Lotus applications.  It contains all the drivers that I know about, including PostgreSQL, SQLite, etc. as well as dBase at ODBC version 3, as well as version 3 and version 3.5 ODBC Driver Managers.

You can use this installation to switch between your Intersolv drivers licensed for Lotus SmartSuite and other drivers including version 3.5 without rebooting.  Instructions are in the ReadMe.Txt in ODBCFILES.ZIP

If you want ODBC version 3 files that are evaluation license get ODBC_3-01.zip from hobbes.

« Last Edit: February 23, 2024, 08:08:30 pm by Doug Clark »

Doug Clark

  • Sr. Member
  • ****
  • Posts: 305
  • Karma: +7/-1
    • View Profile
Re: SQL running total
« Reply #8 on: February 23, 2024, 08:15:57 pm »
I have attached the program we talked about.

This is an app that I first wrote years ago to handle some specific database needs.  It is not complete and probably never will be.  But while it was a number of warts and is missing some features, it will run SQL queries against dDbase files using version 3 ODBC drivers.  Which means it will handle your SQL statement, and the version 3 driver supports renaming of columns in the result set with AS

To get line numbers for your results (what you call sequence numbers) type
SET SHELL ROWNUMBERS=YES before you execute your query.  They will appear on the left instead of the right as you show.


Doug Clark

  • Sr. Member
  • ****
  • Posts: 305
  • Karma: +7/-1
    • View Profile
Re: SQL running total
« Reply #9 on: Today at 01:30:57 am »
Oops - I realize that some of the entries in ODBCINST.INI still had the path to my Lotus SmartSuite install.   Which will cause an error of not being able to find the driver or setup routines.

These new files have the paths removed - because you really don't need to put them in either ODBC.INI or ODBCINST.INI.  ODBC can find them on the LIBPATH.

Sorry.