I must be dense at the moment. I failed to see mismatch between

declare holdgDetl cursor  
 for select valuation_dt, fi_sym, Qty, lst_qot, Cost, mkt_value, xchg_rate
 from dba.PF_Daily_Holdg_detl

and

FETCH NEXT holdgDetl
  into @Valuation_dt, @fi_sym, @Qty, @lst_qot, @Cost, @mkt_value, @xchg_rate
IF @@FETCH_STATUS <> 0

the exact message is

Could not execute statement.
Wrong number of variables in FECTH
SQLCODE=-264, ODBC 3 STATE='07029'
LINE1, COLUMN 1

below is the abbreviated code

Begin
// report stock holding summary by fi_sym in local currency
 declare @Valuation_dt DATE
// set @Valuation_dt='2019-04-17'
 declare @Fi_Sym VARCHAR(20)
 declare @Qty T__Qty_stock
 declare @Lst_Qot NUMERIC(9,4)
 declare @Cost T__money_2
 declare @Mkt_Value T__money_2
 declare @xchg_rate smallmoney  
 declare @Fi_SymPrev VARCHAR(20)
 declare @QtyTtl T__Qty_stock
 declare @CostTtl T__money_2
 declare @Mkt_ValueTtl T__money_2
 declare @PNLTtl T__money_2
 create table #holdg(
     Valuation_dt DATE NOT NULL,
     Fi_Sym VARCHAR(20) NOT NULL,
     Qty T__Qty_stock NULL,
     Cost T__money_2 NULL,
     Mkt_Value T__money_2 NULL,
     PNL T__money_2 NULL,
     pc smallmoney
)
declare holdgDetl cursor  
 for select valuation_dt, fi_sym, Qty, lst_qot, Cost, mkt_value, xchg_rate
 from dba.PF_Daily_Holdg_detl
 where sub_acct_alias_dd not like '%mom%' and valuation_Dt='2019-04-17'//@Valuation_dt
 order by fi_sym,xchg_rate
//
select 0,0,0,0,0 into @CostTtl,@QtyTtl,@CostTtl,@Mkt_ValueTtl
open holdgDetl
FETCH NEXT holdgDetl
 into @Valuation_dt, @fi_sym, @Qty, @lst_qot, @Cost, @mkt_value, @xchg_rate 
//... omitted statements for brevity
end

asked 30 Sep, 03:00

gg99's gravatar image

gg99
202262736
accept rate: 0%

edited 30 Sep, 03:05


select 0,0,0,0,0 into @CostTtl,@QtyTtl,@CostTtl,@Mkt_ValueTtl

Five values for four variables? - I guess that throws that error.

FWIW, the following code mimics that situation:

begin
   declare n1, n2, n3, n4 int;
   select 0,0,0,0,0 into n1, n2, n3, n4;
end;

and also raises SQLCOODE -264:

"Could not execute statement. – Wrong number of variables in FETCH"

permanent link

answered 30 Sep, 03:33

Volker%20Barth's gravatar image

Volker Barth
34.4k335490723
accept rate: 33%

edited 30 Sep, 04:22

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21

question asked: 30 Sep, 03:00

question was seen: 88 times

last updated: 30 Sep, 04:22