OS/2, eCS & ArcaOS - Technical > Programming

SQL running total

<< < (2/2)

roberto:

--- Quote from: Per E. Johannessen on February 18, 2024, 06:51:40 pm ---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?

--- End quote ---
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: ---AST:DBERUNMACRO('MNUMERAR',ASIEN)

--- End code ---
and a new macro like this:

--- Code: ---/*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


--- End code ---
saludos

Per E. Johannessen:
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:
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.

Doug Clark:
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:
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.

Navigation

[0] Message Index

[*] Previous page

Go to full version