cancel
Showing results for 
Search instead for 
Did you mean: 

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

g_g_99
Participant
0 Kudos
2,267

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

chris_keating
Product and Topic Expert
Product and Topic Expert

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
g_g_99
Participant
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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?

g_g_99
Participant
0 Kudos

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