|
|
Posted by Ed Murphy on 11/29/06 09:12
Mohd Al Junaibi wrote:
> select distinct inventory.location store
> from item, inventory, companies cmp
> where ( item.itemnum = inventory.itemnum )
> and ( item.in9 <> 'I' or item.in9 is null )
> and ( inventory.location = cmp.company )
> and inventory.location not in('WHHQ')
> and cmp.registration2 not in
> ('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
> group by cmp.registration2, inventory.location
>
> This query returns the inventories I need to calculate on, and I place
> a cursor based on the above query, and run through each calculation
> with the cursor.
>
> At the end of each calculation, an INSERT statement is done to one
> table:
>
> INSERT STATEMENT:
>
> insert into invsum ( STORELOC, RECEIPTS, RETURNS, TRANSFERIN, WRITEON,
> ISSUES, TRANSFEROUT, WRITEOFF, OPENBAL, CALCLOSEBAL, INVENTORYVAL,
> OPENBALDATE, CLOSEBALDATE ) values(@StoreName2,
> @StrReceipts,@StrReturns,@StrTransfersIn,@StrWritesOn,@StrIssues,@STrTransfersOut,@StrWritesOff,@LastClose,@StrCalculatedBal,@StrMaxInvVal,@startDate,@endDate
> )
>
> Each @ variable from a particular calculation.
We need to see these calculations.
> How can I optimize my cursor? I'm thinking of making a variable of the
> above query with varchar (300)..and then running it into...ok...I'm
> lost.
You want to /eliminate/ all cursors, if at all possible.
Possible approach:
// Populate the STORELOC column
insert into invsum (STORELOC)
select inventory.location
from item
join inventory on item.itemnum = inventory.itemnum
join companies on inventory.location = cmp.company
where not (item.in9 = 'I')
// alternative: where coalesce(item.im9,'') <> 'I'
and inventory.location <> 'WHHQ'
and cmp.registration2 not in
('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
group by inventory.location
// Populate the first couple calculations
update invsum
set receipts = it_receipts, returns = it_returns
from invsum join (
select storeloc,
sum(case when amount > 0 then amount end) receipts,
sum(case when amount < 0 then amount end) returns
from invtran
group by storeloc
) on invsum.storeloc = invtran.storeloc
This is more complex SQL than I usually have occasion to write,
so proofreading would be much appreciated.
[Back to original message]
|