IQ SQL Error: Correlation name 'updates_cur' not found
Hi i have a procedure with a cursor. Basically in the cursor i am getting a record and inserting it into DBA.header_balancing with certain values that was received in the cursor. I receive this error "Error: Correlation name 'updates_cur' not found"
CREATE PROCEDURE sp_iq_bw_balancing
AS
BEGIN
DECLARE @date_Var date
SET @date_Var = CONVERT(CHAR(10),datepart(yy,getdate())||'-'||datepart(mm,getdate())||'-'||datepart(dd,getdate()))
declare updates_cur cursor
for select region
from DBA.TEST_IMPORT_CSV
OPEN updates_cur
BEGIN
/*Header */
INSERT INTO DBA.header_balancing(region,store_no,start_date,tran_id,start_hour,start_minute,start_second,employee,freq_shopper,lane_no,tran_no,end_da开发者_如何学Cte,end_hour,end_minute,end_second,total_items,total_amount,total_tenders,load_date)
VALUES (updates_cur.region, updates_cur.store_no, updates_cur.tran_date,'9999999999','23','59','59','999999999','N','999','999999',updates_cur.tran_date,'23','59','59','1',updates_cur.iq_variance_sales,'1',date_Var)
END
CLOSE updates_cur
DEALLOCATE CURSOR updates_cur
END
go
Execute sp_iq_bw_balancing
If this is a Sybase IQ cursor then I think you need to do this, 1) change the OPEN
to OPEN WITH HOLD
. Then FETCH
the value of "region,store_no,tran_date,iq_variance_sales INTO @region,@store_no,@tran_date,@iq_variance_sales" variables and then insert the variable values.
Also your original code above tries to insert four columns (region, store_no,tran_date,iq_variance_sales) from the cursor but the cursor SELECT only includes the first column (region)
Something like....on Sybase IQ
CREATE PROCEDURE sp_iq_bw_balancing
AS
BEGIN
DECLARE @region VARCHAR
DECLARE @store_no VARCHAR
DECLARE @tran_date DATE
DECLARE @iq_variance_sales VARCHAR
DECLARE @date_Var date
SET @date_Var = CONVERT(CHAR(10),datepart(yy,getdate())||'-'||datepart(mm,getdate())||'-'||datepart(dd,getdate()))
declare updates_cur cursor
for select region,store_no,tran_date,iq_variance_sales
from DBA.TEST_IMPORT_CSV
OPEN updates_cur WITH HOLD
FETCH updates_cur INTO @region,@store_no,@tran_date,@iq_variance_sales
WHILE (@@sqlstatus = 0)
BEGIN
/*Header */
INSERT INTO DBA.header_balancing(region,store_no,start_date,tran_id,start_hour,start_minute,start_second ,employee,freq_shopper,lane_no,tran_no,end_date,end_hour,end_minute,end_second,total_items,total_amount,total_tenders,load_date)
VALUES (@region, @store_no, @tran_date,'9999999999','23','59','59','999999999','N','999','999999',@tran_date,'23','59','59','1',@iq_variance_sales,'1',date_Var)
FETCH NEXT updates_cur INTO @region,@store_no,@tran_date,@iq_variance_sales
END
CLOSE updates_cur
DEALLOCATE CURSOR updates_cur
END
精彩评论