please help me with this query!

[sixtoedsloth]

Hi, i hope this isnt to much to ask!

I have a database that has tables : -
batch
batchLines
buyers
commissionSpec

I need to give a date range and return all the batch records that are between the dates, joined with the batchLines for each batch.

so far so easy!

then for each batch there is a status flag, if this is 'M' then i need to multiply the count of batchLines for that batch by the value of buyers.commission and store the result in batch.commission

OR

if batch.status = 'P' i need to cycle through all the batchlines and check if the batchLine.grade is 'IN' a field
containing valid grades that resides in commissionspec which has a PK which is the FK in batches and store that in batch.commission

batch.buyer = '02'
batch.status = 'P'
batchline.grade = '3L'
commisionspec.buyer = '02'
and the commisionspec.spec = '3L4L3H4H'


then produce a buyers commision report

I suppose the question is this ;


is this going to be viable in pure sql, or should i use VB to do the processing into some temp tables and then report on them?

the backend database is powerflex, and im using the connx odbc driver to access the data.


I appreciate / assume this is a tricky request, and the customer wont let me post data here for 'confidentiality reasons' so i would value your opinions!


Cheers

Six

 

[rac2]

I don't believe there is a general answer to your questions. In fact there may be more alternatives than you have suggested. It may be possible to do some of the work in stored procedures using cursors. Just what you wanted, more decisions.

SQL is good for storing and retrieving sets of rows. It is straightforward to calculate using values within a single row. If you need to calculate or compare data from different rows, then you will find it rather awkward if not impossible to do this with SQL. VIEWs that provide aggregate values, SUM, MIN, etc., self-joins, and CASE expressions can take you a little distance down this road.

But there are many things that are only possible, or more straighforward in a procedure language such as VB. However, RDBMSystems sometimes have procedure languages. These can be more efficient than VB. A rule of thumb, use VB for the user interface, do the computations in a stored procedure. Especially if the computations depend on retrieving and storing data from a database.

Hope this helps.

 

[sixtoedsloth]

yes thanks,

since my dbms has no stored procedures i shall have to use
VB to do the calculations and writing back, then its a simple case of writing the report. i thought it would be the case, but i like to look for other ways of doing things!


thanks for responding


Russ

 

[JonFer]

Ideally, the commission spec would be a separate table with one row for each valid 2-character code. Then the fields can be indexed and joined.

You should be able to calculate the total commission in a single query with the correct WHERE clause.