February 10, 2005 at 3:55 pm
In access or visual basic you can play with multiple DAO or
For example
Recordset A or TABLE A
PLANT FACTOR
3001 1.5
Recordset B or TABLE B
STOCK PLANT VALUE
12546 3001 $1005.05
12548 3001 $5856.05
12547 3001 $6858.23
So to match off a recordset A against recordset B to multiply the recordset B field ‘Value’ by the field ‘Factor’ in recordset A. The code would be something like this in visual basic
Do until Recordset A Ends ( EOF)
Do until Recordset B Ends ( EOF)
Variable = Recordset B field ‘Value’ multiplied by Recordset A field ‘Factor’
Recordset B field ‘Value’ = Variable
Recordset B move to next record
Recordset A move to next record
IN TSQL you could do this via JOINS etc, but can you play with recordsets like this in TSQL using WHILE program flow method..
I know that these two select statements return a recordset
SELECT * FROM Table A
SELECT * FROM Table B
So how can you play with recordsets like I did in visual basic in TSQL, using WHILE or other looping method ??? Even if its not the best was to get good performance TSQL.
February 10, 2005 at 4:15 pm
See BOL on T-SQL syntax for WHILE, DECLARE CURSOR and FETCH NEXT. Using these you can build something functionally equivalent.
However, just because you can do this, doesn't mean you should.
I once worked on a client's "performance problem" that turned out to be:
Do until Recordset A Ends ( EOF)
intCount = intCount + 1
Recordset A move to next record
Loop
.. as a way to find the number of records in a table. Worked great on the developer's 100 row test table, not so well on 700K rows after 6 months of deployment.
All because of a developer's VB-bias, and failure to understand Select Count(*) ..
February 10, 2005 at 5:13 pm
I understand CURSORS are not good to use, So I guess WHILE is not either on large tables.
Better to use CREATE TABLE, INSERT, SELECT on large recordsets.
Just wanted to know how it would be done in TSQL...
February 24, 2005 at 1:54 am
If you are actually attempting to change the value by factor for all records in TableB, use t-sql code like the following. This uses the power of sql set theory, rather than one record at a time as your VBA code would do:
update TableB
set [Value] = [Value] * TableA.Factor
from TableB join TableA
on TableB.Plant = TableA.Plant
If you want to see what the the result might be first, use the following code. This does assume that the field Plant is unique within TableA and you have a value for each Plant.
select TableA.Plant, TableA.Factor
, TableB.Stock, TableB.Plant, TableB.Value as BeforeValue
, ( TableB.Value * TableA.Factor ) as AfterValue
from TABLEA join TABLEB on TableA.Plant = TableB.Plant
Robert
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply