I must have made a mistake somewhere in this batch sql. however I failed to find after hours of review and web search

> Begin
    // report stock holding summary by fi_sym in local currency
    declare @Valuation_dt DATE
    set @Valuation_dt='2019-04-17'
    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
//  and fi_sym='csco' and currency='cad'
    order by fi_sym,xchg_rate

    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

    select 0,0,0,0,0 into @CostTtl,@QtyTtl,@CostTtl,@Mkt_ValueTtl
    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

    open holdgDetl

    FETCH NEXT FROM holdgDetl  // ** here is the point of sybase central complain
     into @Valuation_dt,@fi_sym,@Qty, @lst_qot, @Cost,@mkt_value,@xchg_rate
    //valuation_dt,fi_sym,Qty, lst_qot, Cost,mkt_value,xchg_rate
    IF @@FETCH_STATUS <> 0 print '         <<none found="">>          '
    set @Fi_SymPrev=@fi_sym

the declare of variables used were copied from the definition of the source table.

asked 24 Apr, 11:56

gg99's gravatar image

accept rate: 0%

edited 24 Apr, 12:13

Volker%20Barth's gravatar image

Volker Barth


I guess the "FROM" is wrong here...

(24 Apr, 12:18) Volker Barth

The FETCH statement does not have a syntax FETCH NEXT FROM. It should be

FETCH NEXT holdgDetl INTO  @Valuation_dt,@fi_sym,@Qty, @lst_qot, @Cost,@mkt_value,@xchg_rate
permanent link

answered 24 Apr, 12:23

Chris%20Keating's gravatar image

Chris Keating
accept rate: 32%

thank you all. Chris you are right on.

however it now complain about wrong number of variable in fetch.

I counted the cursor select - 7 columns and I have 7 variables in fetch. moreover I copied the column names in cursor and paste below the fetch to match one column with one variable. Failed to find the stupid mistake sybase central complains about.

looks like a rough day.

(24 Apr, 18:28) gg99

I think you have to trust the error which I believe is -264 SQLE_WRONG_NUM_OF_FETCH_VARIABLES. I did attempt to repro using the SQL posted but there is a number of unknowns i.e, domains but did not hit any errors. Perhaps you can enable a request level log ( -zr sql+hostvars ) and review it.

Additionally, what version and build are you using?

(24 Apr, 22:28) Chris Keating

sorry got busy on other urgent tasks. Interactive Sql version 11.01 build 3406 Sybase central Sql Anywhere 11 version

apparently there is an update 11.0.1 build 3158. however I can't sign in as I don't have access to SAP Service Marketplace account

(01 May, 19:35) gg99
Your answer
toggle preview

Follow this question

By Email:

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



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:


question asked: 24 Apr, 11:56

question was seen: 72 times

last updated: 01 May, 19:35