ASE Database forum - how to run the store procedure??

Author

how to run the store procedure??

Kin

2005-04-15, 3:24 am

create procedure dayend_undo
as

declare @settle_date datetime , @trade_date datetime , @prev_date
datetime

select @trade_date = trade_date, @settle_date = settle_date
from system_master
where sys_code = 'SECURIT'

delete client_balance
where month_end = @trade_date

delete client_inventory
where month_end = @trade_date

delete depot_inventory
where convert(char(8), month_end, 112) = convert(char(8), @trade_date
, 112)

delete client_holding
where month_end = @trade_date

delete settle_detail
where settle_date = @trade_date
/* trigger will update trades_master.settled_amount and
trades_master.settled_qty incorrectly when there is
more than 1 detail with the same trade_id deleted
*/

delete depot_settle_detail
where settle_date = @trade_date
/* trigger will update depot_trades.settled_amount and
depot_trades.settled_qty incorrectly when there is
more than 1 detail with the same trade_id deleted
*/

delete depot_trades
where settle_date = @settle_date

delete voucher_detail
from voucher_master, voucher_detail
where voucher_master.voucher_key = voucher_detail.voucher_key
and eff_date = @trade_date
and voucher_type <> 8

delete voucher_master
where eff_date = @trade_date
and voucher_type <> 8

update voucher_master
set status = 'Y'
where eff_date = @trade_date
and status = '*'

update tt_voucher_master
set status = 'Y'
where eff_date = @trade_date
and status = '*'

update stock_voucher
set status = 'Y'
where eff_date = @trade_date
and status = '*'

update location_transfer
set status = 'Y'
where eff_date = @trade_date
and status = '*'

update depot_transfer
set status = 'Y'
where convert(char(8), eff_date, 112) = convert(char(8), @trade_date ,
112)
and status='*'

update trades_master
set settled_qty = 0,
settled_amount = 0,
full_settle_date = null
where settle_date = @trade_date

select @prev_date = max(month_end)
from client_balance
where month_end < @trade_date

exec copy_balance @prev_date, @trade_date

update system_master
set client_settled = 'N',
depot_settled = 'N',
calc = 'N',
trade_matched = 'N',
trade_voucher = 'N',
settle_voucher = 'N'
where sys_code = 'SECURIT'

exec dayend_undo???

if I can undo the dayend.
My system will be fine but i don't know how to run this procedure

Bret Halford

2005-04-15, 7:24 am

To execute this procedure, assuming it exists in the system,
all you would have to do is enter

use <the correct database name>
go
exec dayend_undo
go

in an ISQL session.

If that isn't working for you, what errors do you get when you try it?

-bret

Kin wrote:

> create procedure dayend_undo
> as
>
> declare @settle_date datetime , @trade_date datetime , @prev_date
> datetime
>
> select @trade_date = trade_date, @settle_date = settle_date
> from system_master
> where sys_code = 'SECURIT'
>
> delete client_balance
> where month_end = @trade_date
>
> delete client_inventory
> where month_end = @trade_date
>
> delete depot_inventory
> where convert(char(8), month_end, 112) = convert(char(8), @trade_date
> , 112)
>
> delete client_holding
> where month_end = @trade_date
>
> delete settle_detail
> where settle_date = @trade_date
> /* trigger will update trades_master.settled_amount and
> trades_master.settled_qty incorrectly when there is
> more than 1 detail with the same trade_id deleted
> */
>
> delete depot_settle_detail
> where settle_date = @trade_date
> /* trigger will update depot_trades.settled_amount and
> depot_trades.settled_qty incorrectly when there is
> more than 1 detail with the same trade_id deleted
> */
>
> delete depot_trades
> where settle_date = @settle_date
>
> delete voucher_detail
> from voucher_master, voucher_detail
> where voucher_master.voucher_key = voucher_detail.voucher_key
> and eff_date = @trade_date
> and voucher_type <> 8
>
> delete voucher_master
> where eff_date = @trade_date
> and voucher_type <> 8
>
> update voucher_master
> set status = 'Y'
> where eff_date = @trade_date
> and status = '*'
>
> update tt_voucher_master
> set status = 'Y'
> where eff_date = @trade_date
> and status = '*'
>
> update stock_voucher
> set status = 'Y'
> where eff_date = @trade_date
> and status = '*'
>
> update location_transfer
> set status = 'Y'
> where eff_date = @trade_date
> and status = '*'
>
> update depot_transfer
> set status = 'Y'
> where convert(char(8), eff_date, 112) = convert(char(8), @trade_date ,
> 112)
> and status='*'
>
> update trades_master
> set settled_qty = 0,
> settled_amount = 0,
> full_settle_date = null
> where settle_date = @trade_date
>
> select @prev_date = max(month_end)
> from client_balance
> where month_end < @trade_date
>
> exec copy_balance @prev_date, @trade_date
>
> update system_master
> set client_settled = 'N',
> depot_settled = 'N',
> calc = 'N',
> trade_matched = 'N',
> trade_voucher = 'N',
> settle_voucher = 'N'
> where sys_code = 'SECURIT'
>
> exec dayend_undo???
>
> if I can undo the dayend.
> My system will be fine but i don't know how to run this procedure

Kin

2005-04-15, 8:24 pm

i have try it be4
it show
1row affected
return status =0
but my system haven't undo the dayend
"Bret Halford" <bret@sybase.com> wrote in message
news:425FB554.F02277F@sybase.com...
> To execute this procedure, assuming it exists in the system,
> all you would have to do is enter
>
> use <the correct database name>
> go
> exec dayend_undo
> go
>
> in an ISQL session.
>
> If that isn't working for you, what errors do you get when you try it?
>
> -bret
>
>
> Kin wrote:
>
@trade_date[color=da
rkred]
@trade_date ,[color=darkred]
>

Mark A. Parsons

2005-04-15, 8:24 pm

1 - problem with your logic that's suppose to undo the dayend, ie, it's not
coded the way you *think* it should be coded?

2 - a trigger on one (several?) of your tables that is undoing the undo, ie,
rolling back your 'DELETE'?

Kin wrote:
[color=darkred]
> i have try it be4
> it show
> 1row affected
> return status =0
> but my system haven't undo the dayend
> "Bret Halford" <bret@sybase.com> wrote in message
> news:425FB554.F02277F@sybase.com...
> @trade_date
> @trade_date ,

Bret Halford

2005-04-17, 9:23 am

One possible problem I see with this procdure is that
it starts by populating several variables from the system_master
table where sys_code = "SECURIT". This puts the values from the
last row of that result set into the variables. At the end of the procedure, it

updates system_master for ALL rows where sys_code = "SECURIT".
It seems to me this behavior may not be correct if there are multiple
rows in that result set (presumably with slightly different trade_date
and settle_date values - I'm assuming datetime datatypes with time components
are being used here).

-bret

..

"Mark A. Parsons" wrote:
[color=darkred]
> 1 - problem with your logic that's suppose to undo the dayend, ie, it's not
> coded the way you *think* it should be coded?
>
> 2 - a trigger on one (several?) of your tables that is undoing the undo, ie,
> rolling back your 'DELETE'?
>
> Kin wrote:
>

LINCOLN

2005-04-17, 8:24 pm

thank you all i have success run the dayend_undo procedure
but in a bad method =.= i have cut the procedure in many parts & run it
directly (not useing procedure )it can run & delete the data in database i
don't know y it can't run in procedure mode but anyway i have fixed all the
problem & my system is ok now thank you very much
"Bret Halford" <bret@sybase.com> wrote in message
news:42626AAA.2D0F7DCE@sybase.com...
> One possible problem I see with this procdure is that
> it starts by populating several variables from the system_master
> table where sys_code = "SECURIT". This puts the values from the
> last row of that result set into the variables. At the end of the
> procedure, it
>
> updates system_master for ALL rows where sys_code = "SECURIT".
> It seems to me this behavior may not be correct if there are multiple
> rows in that result set (presumably with slightly different trade_date
> and settle_date values - I'm assuming datetime datatypes with time
> components
> are being used here).
>
> -bret
>
> .
>
> "Mark A. Parsons" wrote:
>
>

Sponsored Links