开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜