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 '19, 11:56

gg99's gravatar image

gg99
227293038
accept rate: 10%

edited 24 Apr '19, 12:13

Volker%20Barth's gravatar image

Volker Barth
39.5k355539810

FETCH NEXT FROM holdgDetl

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

(24 Apr '19, 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 '19, 12:23

Chris%20Keating's gravatar image

Chris Keating
7.0k45116
accept rate: 30%

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 '19, 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 '19, 22:28) Chris Keating

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

(01 May '19, 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

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:

×143
×23
×8

question asked: 24 Apr '19, 11:56

question was seen: 1,426 times

last updated: 01 May '19, 19:35