cursor fetch trouble: could not execute statement. Syntax error near (fetch) 'FROM)

[VolkerBarth]

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.

[VolkerBarth]

FETCH NEXT FROM holdgDetl

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

[chris_keating]

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

[Former Member]

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.

[chris_keating]

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?

[Former Member]

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

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