Posts by tesuji

Hi andydeans

Still about solving your multi-select problem? I am sure that it can be solved in ways that I already ilustrated in which you should create a view consisting of 6 or 7 selects on your various products and aggregated them by UNIONs.

Once the view exists then totals depending on due dates and other grouping criteria can easily be generated by means of simple selects. You should really take into consideration my example given in: http://www.daniweb.com/forums/thread288573-2.html, it functions in this way. (I am doing such design tasks day in, day out on a truly sophisticated database having quite a few thousands tables.)

-- tesu

Well, primary key, foreign key, not null, unique, check clause, also sometimes triggers
etc. are or may define constraints. So which of them are meant?

-- tesu

Hi

in JavaDB (same as Derby) columns which are foreign keys can be denoted by: CONSTRAINT NameOfConstraint REFERENCES refTable(PK_of_refTable), see Derby manual p.68. There are also conventional table-level foreign key constraints like: FOREIGN KEY (a, b, c) REFERENCES refTable (a, b, c), see Derby manual p.72.

btw, I think that the code fragment posted in by you should be more complete and should show your try of defining foreign keys at least.

-- tesu

Hi
Karol has already enumerated all most important advantages of varchar over text DT, so it seems rather impossible to abandon varchar without good cause.

As for your five-column table: What do you further want to do with PK NoteID? Isn't there a true natural primary key given by triple (NoteUserID, NoteClientID, DateCreated). One should also taken into account that (NoteUserID, NoteClientID) obviously defines a many-to-many relationship between entities USERS and CLIENT.

-- tesu

Well, no doubt :-/

One should consider some drawbacks of datatype text over varchar, for example attributes of datatype text as well as blobs will be stored separately from all other attributes (they are not stored within contiguous pages!), also if text attributes appear together with other attributes in a select statement, the resultset won't never be hold in main memory completely but be stored on disk. There are further limitations if attributes of text datatype be compared, sorted, manipulated with string functions etc.

So, if one doesn't need to handle more then 64k characters, why should he be burden with such unfavorable stuff? Therefore, varchar datatype is the far more appropriate datatype for dealing with such short notes, there is really no doubt whatsoever ;)

-- tesu

hi andydeans,

I didn't forget you. The reason why I didn't respond earlier is I haven't as yet accessed to a MySQL database, only MS SQL Server and Sybase at hand to date. So I couldn't test the following sql statements.

CREATE OR REPLACE VIEW sumsum( Product, Client, Firstname, Lastname, Total) AS
  SELECT 'generalinsurance', c.clientsName, u.FirstName, u.LastName, sum(p.AmountGI) as sub_amt 
   FROM users u, clients c, generalinsurance p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidGI IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'protection', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, protection p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'buytolet', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, buytolet p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'mortgage', c.clientsName, u.FirstName, u.LastName, sum(p.amount) as sub_amt 
   FROM users u, clients c, mortgage p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaid IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'referrals', c.clientsName, u.FirstName, u.LastName, sum(p.Commission) as sub_amt 
   FROM users u, clients c, referrals p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidReferral IS NULL 
	  GROUP BY c.clientsName, u.FirstName, u.LastName 
UNION
  SELECT 'overseas', c.clientsName, u.FirstName, u.LastName, sum(p.Commission) as sub_amt 
   FROM users u, clients c, overseas p  
    WHERE u.UserID = c.ClientUserID AND c.CientID = p.Clients_ClientID  
	 AND DatePaidOverseas IS NULL
      GROUP BY c.clientsName, u.FirstName, …

Hi

Possibly you can start designing your triggers with oracle sql developer until they function properly. Then you can insert the final result in your Borland c++ program working together with BDE. By Acting this way you will feel certain that arising problems are only localized at turbo c++ or more likely at BDE.

Note btw, because this is a trigger fired every time a new record is inserted in table test which should insert a new generated sequence number, why not simply insert the sequence number in the original insert statement like in this way:

insert into test (id, ...) values (test_seq.NEXTVAL, ...);

which would do exactely the same task?

Note btw2: there is also a special Oracle database forum here, what seems to be a better place to post questions regarding oracle DB.

-- tesu

hi andydeans

date or timestamp datatype is quite usual. If you want to group by month, you can apply month() or monthname() function, as I already stated in:

SELECT MONTHNAME(datePaid) as Mois, sum(amount) as monthly_Mortgage FROM mortgage
      WHERE DatePaid IS NULL GROUP BY Mois;

Recording year, month, day separately isn't that a good idea for one usually deals with complete date, also compact date is easier to handle within a program. If one occasionally needs elements of date-type attributes, applying appropriate functions is efficient.

-- tesu

Hi tyson
I am glad to meet you here :) Let's given a table TuttiFrutti:

Fruits    	onStock		Location
--------------------------------------------
Apples          100			ABC
Oranges		300			BCA		
Plums		800			CBA
Oranges		200			BAC
Plums		100			CAB
Apples		150			ACB
...

Now consider these four select statements:

select fruits, locations, sum(onStock) from TuttiFrutti group by fruits, locations;
-- table TuttiFrutti will be produced completely for there is nothing to group by

select fruits, sum(onStock) from TuttiFrutti group by fruits; 
fruits		sum(onStock)
------------------------
Apples		250
Plums		900
Oranges		500

select sum(onStock) from TuttiFrutti;
sum(onStock)
------------
1650

select fruits, sum(onStock) from TuttiFrutti; --(won't never be executed on serious DBs)
-- Can not sum up apples and oranges?

Note btw, SQL-2003 Standard defined important expansions for group-by clause for doing OLAP, e.g. grouping sets, rollup, cube() which are very useful for creating complex queries.

I hope above examples do clarify something.

-- tesu

Well, I think what manutd4life is looking for is the run-length encoding algorithm, a simple algorithm for lossless data compression in which a sequence of the same character (Byte value) is stored as a single character (Byte value) following by its count, e.g. sequence aaaaaabbbc is RLE-coded by a6b3c1, just as manutd4life already explained (however, more usual is 6a3b1c coding).

There can be a vast number of code snippets found on the web, just searching for "run length encoding c++".

-- tesu

Hi,

here is some answer to your first question: "toatal due query: I got some great help on the forum to create my total due query. However it produces it with just total due, how could i have it by month?"

Answer (code not tested):
You can apply aggregate functions such as sum(), min(), avg() etc together with expressions or simple attributes of a table. For example, if you want to group various amounts by the month they accrued from together with the monthly subtotal you should code:

select monthname(datePaid) as Mois, sum(amount) as monthly_Mortgage from mortgage
  where DatePaid IS NULL  group by Mois;

Output could look like:
Mois_________monthly_Mortgage
January______3,541.58
February_______999.87
etc.

Important rule: All attributes appearing in the select statement which are not part of an aggregate function must be specified in the group-by clause, e.g.

select a, b, c, avg(d), min(e) from x where... group by a, b, c; -- correct clause
select a, b, avg(d), min(e) from x where... group by a; -- wrong clause, missing attribute b

(consider, there are about 30 useful aggregate functions)

Maybe you will get inspired by this group-by clause how to solve "I would like to show the users name along with the clients name in the query."? I'll noddle over this soon, and will be answering asap. To solve this task several tables must be joined together: referrals inner join clients inner join users, where "referrals" surrogates further tables.

well, it might be very helpful to know all related tables and all properly set-up primary and foreign keys. Without these pieces of information it's rather impossible to help you seriously (but pretty kind of stroking the crystal ball).

-- tesu

Hi

Usually I don't meddle in an advanced discussion, however I've got the feeling that this update problem (may be also other) coheres with the overall design. Could you post all tables which are related to pipeline_commission? Have all primary keys and foreign-key constraints been properly set up?

Btw, in "SELECT clients_ClientID, SUM(columnName1) ...." group-by clause is a must.

-- tesu

Yes, you can use SQL Server Management Studio this way:
security --> logins --> new login
--> general: create new login
--> server roles: mark at least sysadmin (there is no admin, sa is obsolete)

etc.

-- tesu

Hello rich_m,

how are you?

I am afraid you will have to wait at the Greek calends.

Once MySql company had a wonderful oracle clone. It was MaxDB, formerly SAPDB. And lots of people thought they would drop ugly MySql database and replace it by MaxDB. But they returned it back to SAP in 2007.

krs,
tesu

hi velodrom

SELECT current_date() - INTERVAL 1 DAY AS "Help yesterday !",   
    current_date() + INTERVAL 1 DAY AS "for tomorrow never come !";

may also give some results.

krs, tesu

Hello

that depends on your specific database system. There are Database systems where you can
type in: select myBirthday - now() as "My age is:" ;

You need to know the current-date function of your database, for example: today(), now(), currentdate() etc.

krs,
tesu

Hi, I'm developing issues database. How to get the ageing or how long the issues has been outstanding. TQ

Hello

Your mistake! You cannot do that !

-----
tesu

Hi,
How can i pick next or prevoius day in mysql? Any function like curdate() ?
Thanks

next: today + 1
previous: today - 1

hello,

>>> "select * from table1 where RAM between ... "

Never use *, the sign of slackers! You must always enumerate only those columns you really need for your datagrid!

There are lots of reasons for data type mismatch, so it s better you bring in the complete and exact error message.

----
tesu

Hello dmmckelv,

When Edgar Frank Codd invited relational databases his primary idea was to found it on
logic algebra and set theory. The rows of relational tables (aka relations) are sets. Therefore, there aren't duplicate rows nor any particular order of rows. From this point of view, "I am looking for something that would give me the speed field value for the row before" is rather irrelevant.

However, to put any meaningful signification on it, your table must already have a column
which allows such an ordering, for example date, timestamp or auto-increment numbers.
If you don't have such special column (or set of columns) within a table, it is impossible to determine the predecessor of a specific row because the today predecessor could be the successor row tomorrow (e.g. due to inserting and deleting rows in the meantime).

To cut a long story short, what's that special column of your table to get particularly ordered rows? You may also post the creating statement of your table.

-----
tesu

Thanks for the quick response!

I am missing one thing though, I do not see Author Name at all in your example?

I did not change your table tbl_Author which already has Author-Name. Therefore I left out your 3rd table !

Hello,

in tbl_poems exists a transitive dependency poem-ID -> Author-ID -> Book-ID. Therefore, this table does not satisfy 3NF. fk Author-ID should be removed and inserted into tbl_Book. The result is:

tbl_Poems:
- poem-ID - pk
- Poem-Title
- Poem
- Book-ID - fk

tbl_Book:
- Book-ID - pk
- Book-Name
- Author-ID - fk

your table structure can easily be put on one form because you have two one-to-many relationships (aka master detail tables). So one author may have many books related to. You can select an existing author or enter a new one in the 1st part of your form. In a 2nd part you can then select an existing book of that author or you may enter a new book where the author_ID would then be inserted into the tbl_book. Once selected a book you will see many poems of that book or in 3rd part of you form you may enter new poems. If you store new poem, current book_ID would be inserted in tbl_poem. Graphical element to hold that data can be combo box, grid element etc.

Ok, details depend on the programming language, database, web server you will use to carry out your poem project.

krs,
tesu

Hi

There is a function LAST_INSERT_ID() what returns the last-created auto-increment value. So first insert parent data what creates a new auto-increment ID. Then insert row in child table where LAST_INSERT_ID() function is put in the values list of the insert statement, for example:

-- first insert parent row
insert into my_contacts (first_name) values ('GFXtm')
-- this generates a new value for auto-increment contactID

-- Now insert child row
insert into interests (interest, contactID) values ('databasing',  LAST_INSERT_ID())
-- this will assign the just generated auto-increment to foreign key contactID

First time, there is no interference between the auto-increment values of both tables: In Mysql all values of the values list will be inserted first. Finally the new auto-increment of table interests will be generated which then become last-inserted ID. That also means that you cannot use above insert statement, if you want to insert multiple children to same parent. For this case you must store the LAST_INSERT_ID() value from parent table into a variable.

krs,
tesu

Hi jakesee

For better handling you need one root only. This can easily be done by defining a master root where all other roots can be formally connected to. Only this master root will not have a parent. Additionally, in my tree table each node has a level number what simplifies traversing the tree.

There is a remarkable paper on

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

which is based on Joe Celko's book: Trees and Hierarchies in SQL for Smarties.

The examples in that paper are mostly based on the nested set model, there is also a short introduction to the adjacency list model and its limitations. Your example is based on that adjacency list model.

I will think over how to traverse a complete tree only by one SQL statement (Actually, I am doing such traversing with C++ program by way of recursive functions because our category tree has some hundreds nodes with extremely various depths). Possibly the new WITH clause of SQL 2003 what has a recursive part may help here. I personally would prefer the nested set model but inserting new nodes in an already existing chain of nodes isn't that easy.

krs,
tesu

hi,

you may google Joe Celko trees

krs,
tesu

You may study programming languages like FORTH or Postscript how they do computing using stacks.

tesu

Ah, you again

Did you proceed in understanding EERM?

I call people who put a magical, universal "id" column with an auto-increment on all their tables "id-iots" ! (Joe Celko)

that s true ! try it without _

-----
tesu

[B]SELECT[/B] Vio.ViolationList.ViolationCode,Records.[Violation Commited],Vio.ViolationList.FineAmnt,DriverInfo.[Plate Number]
,DriverInfo.[License Number],DriverInfo.[Reg'd Last Name],DriverInfo.[Reg'd First Name],DriverInfo.[Reg'd Middle Name],DriverInfo.[Reg'd Address],
DriverInfo.[Address' City Code],DriverInfo.[Reg'd B-Date],DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],DriverInfo.[Vehicle Type],DriverInfo.[Vehicle Brand]
,Records.[Street Name],Records.[Date]
[B]FROM[/B] Vio.ViolationList,DriverInfo,Records
[B]WHERE[/B] Records.PlateNo like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Vio.ViolationList.ViolationD

In above from clause you are joining the tables ViolationList, DriverInfo and Records together. These are inner joins where each row of one table will be combined with each row of second table etc. To prevent such cartesian products you must add join constraints to where clause, for example say you have tableA with columns (a,b) what you want to join with tableB having columns (c, d, e). you want to join them on column a from tableA and column d from tableB, so you have to write:

select a, b, c, d from tableA, tableB where a = d AND ...your further predicates

Join constraint is a = b, which means that only rows will be jointed with same values in both tables.

If you join various tables you must carefully define the joining constraints to avoiding bad cartesian products.

There is a more modern inner join syntax:

select .... from tableA inner join tableB inner join tableC etc

But this requires that primary and foreign keys are properly defined. If not, you have to add the ON clause to each inner join.

krs,
tesu

Hi

Do below statements not work on mysql?

DELETE FROM accounts WHERE usrn = 'caughtusername';
COMMIT;

As nav33n already suggested.

krs,
tesu

amongst other things, it depends on precision...

Hi,

So you have a table consisting of 6 columns: Column Name Data Type Length
Turnover where the latter should be of decimal data type???

What do you mean by "that the max value could be limited to 9999,99" ? Should output format be restricted to 9999.99 (decimal(8,2)) or the value of turnover itself must not exceed 9999.99 what would be a matter of constraints defined in create table statement?

krs,
tesu

Hi veledrom,

I think your SQL is syntactically not corret: If insert is done from result set given by select you must not use values part. Also, if you want to insert two values you must also select two values in each row. So try this:

INSERT INTO table1 (subid, paid)
 SELECT subid, paid FROM def_1 WHERE id IN (SELECT id FROM def_2)

krs,
tesu

Hi tuse

How are you?

Thanks a lot.

Didn't see the 'mysql' database.

Well, if you had access to grant table mysql.user you would have also been able to
SELECT User, Password FROM mysql.user. You got it? Yes, you would be able to hack that
mysql database. So it's a good idea that not everybody is allowed to "see" grant table.

Happy hacking !

krs,
tesu

Hello Shaun32887

following is a small class what has been posted by a programming virtuoso here in forum on daniweb.com. Unfortunately, I have not recorded his name.

//
// String passing
#include <string>
#include <sstream>
#include <iostream>
using namespace std;

class Pars
{
public:
  double bar ( string line )
  {
    stringstream ss ( line );
    string word;
	double x;
    int n = 0;
    while ( ss >> word )
	{
	  istringstream ins;
	  ins.str ( word );
	  ins >> x;
	  cout << n << " " << x << endl;
	  n++;
    }
    return x;
  }
};

int main()
{
  Pars stri;
  string s =  "100 1.745329252 0.984807753 -0.173648178 -5.67128182";
  cout << "Parsing: "<< s << endl;
  stri.bar (s);
  return 0;
}
/* Result
Parsing: 100 1.745329252 0.984807753 -0.173648178 -5.67128182
0 100
1 1.74533
2 0.984808
3 -0.173648
4 -5.67128
*/

As for your permutation problem, I will search my collections. If I make a find, I will send you the code.

krs,
tesu

instead of: int i = ch; // stores same code in an int (wrongly)

you might try: int*i =(int*)ch;

then
cin>>ch; // enter A for example
cout << ch; // gives A
cout << i; // gives 0x41
cout << (int)i; // gives 65

krs,
tesu

Hi

Your code:
char ch;
int i = ch;
You didn't assign a value to ch. So i is undefined. Also this would not work if you do cin >> ch.

You may try this instead:

char xxx = 'x';
cout << "char: " << xxx << " as int: " << (int) xxx << endl;
cout << "next char: " << ++xxx << " as next int: " << (int) xxx << endl;

If you know pointers, there would also be a solution.

crs,
tesu

Elaborate please =], i was trying to say that a-r are random numbers between 1 and 9, this is just a snipet of some code i already have, i an just having some problems with figuring out random numbers... i figured that if the largest variable is an unsigned, max of about 4 billion, and seing as i need a 17 digit number, i generate a number for each digit, when the person continues it prints out each digit in the end

Well dear Superfat, the most effective solution for you problem has already been given by vijayan121. Unfortunately you aren't able to understand his fine function. So GOD's advice should be seriously followed.

Btw, your nice random numbers a..r never contain 9, got it?

krs,
tesu

sorry, completely nonsense what I wrote ! so forget it.
true, new <--> delete and malloc <--> free
sorry for telling that nonsens

krs,
tesu

Hi mrboolf,

. . .

M_Board::M_Board() {
	int i = 0, j = 0;
	m_board = new int*[N];
	for(i=0;i<N;i++) {
		m_board[i] = new int[N];
	}
	i = 0;
	for(i=0;i<N;i++) {
		for(j=0;j<N;j++) {
			m_board[i][j] = 0;
		}
	}
	QCount = 0;
}

M_Board::~M_Board() {
	int i = 0;
	for(i=0;i<N;i++) {
		delete [] m_board[i];
	}
	delete [] m_board;
}

Consider your constructor and destructor:

In C++ memory is allocated by applying "new". Its partner is "free" ! You should never apply "delete", which is plain C, on memory once allocated with "new".

Maybe that will help you.

Btw, you should mark the statements by the line number where the warning appears. So simple add //123: warning: left-hand operand of comma has no effect
at the code line in question.

brs,
tesu

Hi chsarp_vijay,

Derby is pure Java. if you want to use it, you must program Java. Why not using SQL Anywhere/Ultralight from worldwide leader in mobile computing, Sybase? There mobile computing database run on almost all mobile systems, even on mobile phones (also Derby does so). You can download developer system from Sybase without charge and limitations (ok, one limit exists: you shall not use developer licence in commercial projects).

krs,
tesu

Hi
you may start SQL Server Management Studio (If you are using SQL server Express you can download SQL Server Management Studio separately) an then create staff table, enter some rows, for example:

-- create the table
create table staff (
staffID integer not null,
fname varchar(50),
lname varchar(50),
job varchar(20),
salary decimal(8,2),
primary key (staffID) );

-- Add some rows to your table
insert into staff (staffID, fname,  lname, job, salary)
  values (1789, 'George', 'Washington', 'CEO', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (1801, 'Thomas', 'Jefferson', 'Fighter', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (1841, 'John', 'Tyler', 'Whig', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (2001, 'George W.', 'Bush', 'Rep', 10000);
 
-- Select all staff members
select * from staff;

-- Who was first staff member?
select top 1 * from staff order by staffID

Ok, these are some SQL statements to generate staff table interactively by using Management Studio. You can also integrate all above SQL statements into your VB program.
You need to read a book or a tutorial how to do that. (I myself only know c++ and java, no vb knowledge here)

krs,
tesu

Hi niek_e

You are right, I have overlooked the semicolon at he far right.

Intuitively I also would put a semicolon at left side if for-init statement is off. But the ISO standard tells the opposite. You may have a look at this standard on http://www-d0.fnal.gov/~dladams/cxx_standard.pdf
In the lower half page 95 you will find the definition of the for-loop. Where
you can see that the first semicolon is integral part of the for-init statement,
just consider the gap between for-init statement and condition statement, also see
the given note. That means, if one omits the for-init statement the semicolon also
disappears.

Obviously, almost all C++ compilers do not implement the for loop in concordance with the ISO Standard, do they?

krs,
tesu

Hi QuantNeeds

. . .
warning C4552: '>' : operator has no effect; expected operator with side-effect
. . .

int numStudents = 10;  // assignment necessary 
	for(numStudents > 0; numStudents -= 1;)

Your for loop is correct. It corresponds completely with the Standard ISO/IEC 14882:1998(E) of C++. So your compiler seems to be something outdated, especially the phrase "expected operator with side-effect", what means that an for-init statement would have been expected, tells that your compiler isn't that conform to the standard.

brs,
tesu

Hi motofoto

There is kind of inheritance - in EERM - where we have Generalization / Specialization, such called isa-relationships. Maybe you should first design an EERM. Your implementation idea of isa-relationships is correct, generic data might be collected in a person entity (table), specialized data in additional entities (tables, if you map EERM into relational model). Also there are one-to-one relationships between generic entity and specialized entities.

There is no other way to map isa-relationships from EERM into relational model (RM) then yours till this day. Though the latest SQL standard from 2003 has got a little OOP ideas added but it is far from classical OOP inheritance.

One cannot say that your approach would put more complexity into your design. Sure, because of one-to-one relationship you can merge generic and specialized entities into one one, where you would get three independent entities (tables) Client, Owner, Staff. But if the sketched problem is your assignment, probably your prof expects an isa-relationship solution of the problem.

krs,
tesu

As a beginner, after creating tables, what is the next step?

After having done that hard job shutdown server is badly necessary !

Hi all,

though Ancient Dragon has already answered almost every question, I would like to put in my twopenn'orth too.

-Why use the stack of the space is so limited?

Stack is not that limited. One can allocated as much stack as much memory from heap.
Today processors have 32bit stack pointers (e.g. ESP, EBP), what allows one to address some Giga bytes on stack.

-What are the advantages to using the stack over the heap?

Memory on stack will be automatically allocated when variable is to create and
automatically released if variable is no further needed, see below answer to lifetime.
So a programmer does not need care about his variables, except of those which must be
created by new/malloc. Stack is one of the most important data structure or principle in
computer science. Recurrence, especially recursive function calls would really be hard
to implement without stack, or even impossible. On the other hand, every time we don't know the size of an data object when program it, the object must be created, that is its memory must be allocated from heap during runtime. So heap allocation is also essential for programming.

-Is it really so bad to use pointers vs "reference variables?"

I personally think it isn't that bad because there is no difference between them,
except c++ reference variables look more beautiful and accomplish Bjarne Stroustrup's
modern conception …

oops wujtehacjusz, you may have a look at issue date;)

Hello kutta_vin,

well, "forgotten to mention" has created some superfluous work. However it is rather simple to get the same result from your modified tables. Supposing a table act2(AccountId, TransactionID, trtype, trvalue) exists, then there are some possibilities to solve your problem. All solutions that come into consideration are based on new CASE clause (MySql supports it). Then, one can solve the problem by means of 1) stored procedures, 2) SQL views, or 3) WITH clause (MySql 5 has it).

I have used the WITH clause as you can see. With WITH clause first a temporary table tact which selects from your new table structure act2 the old structure with columns transaction, credit and debit. On that temporary table my yesterday posted SQL select can be executed without any changes. Nice, isn't it?

with tact (transaction, credit, debit) as
(
   select TransactionID, 
     case trtype
          when 'Deposit' then coalesce(trvalue, 0)
          else 0
     end as Credit,
     case trtype
          when 'Withdrawal' then coalesce(trvalue, 0)
          else 0
     end as Debit
   from act2 where accountID = 'SA001'
) 
select transaction, credit, debit, credit+coalesce((select sum(credit) from tact b
  where b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((select sum(debit) from tact b
    where b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       from tact a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

I …