Insert into select statement returns error: " SQL Anywhere Error -185: SELECT returns more than one row"

Question

I am using a insert into ... select ... statement to insert multiple rows into a table however, I receive the following error when the statement is executed:

SQL Anywhere Error -185: SELECT returns more than one row.

Here is my code:

begin
declare   @pyear integer= 0
  print 'Step 1 '
  print 'Step 2 '
  declare @startDateYear integer,@llystartDt integer,
  @tradeYear integer,
  @evRefreshDate integer,@err integer,@l_cnt integer
  declare @mtdmin integer
  declare @mtdmax integer
  declare @cyear integer
  declare @cmonth integer
  select @cyear = year,@cmonth = month from table1 where last_trade_date = 'Y'
  select @mtdmin = min(intDate) from table1 where year = @cyear and month = @cmonth
  select @mtdmax = intDate from table1 where last_trade_date = 'Y'
  if @pyear <> 0
    begin
      select @tradeYear = @pYear, @startDateYear = convert(integer,convert(char(4),@pyear)+'0101')
      select @evRefreshDate = @startDateYear
      select @llystartDt = convert(integer,convert(char(4),@pyear-2)+'0101')
    end
  else begin
      select @startDateYear = MIN(dd.intDate)
        from table1 as dd
          join table1 as dd1
          on dd1.year = dd.year
          and dd1.LAST_TRADE_DATE = 'Y'
      select @evRefreshDate = intDate
        from table1 as dd
        where dd.LAST_TRADE_DATE = 'Y'
      select @tradeYear = dd.year
        from table1 as dd
          join table1 as dd1
          on dd1.year = dd.year
          and dd1.LAST_TRADE_DATE = 'Y'
      select @llystartDt = convert(integer,convert(char(4),(@tradeYear-2))+'0101')
    end

  print 'EV Refresh Date =%1!',  @evRefreshDate

  print 'MktType','Dimension','Dimension_Value','cnsEv','YearlyRollup','MonthlyRollUp','DailyRollup','YearlyDiff','MonthlyDiff','DailyDiff','stat'

  print 'Step 3 '

  print 'Step 4 '

   --truncate table table2
  insert into table2
    ( MktType,
    Dimension,
    Dimension_Value,
    cnsEv,
    YearlyRollup,
    MonthlyRollUp,
    DailyRollup,
    YearlyDiff,
    MonthlyDiff,
    DailyDiff,
    stat ) 
    select 'MktType'='FI',
      'Dimension'='' || 'TradeDate',
      'Dimension_Value'='' || @tradeYear,
      'cnsEv'=cns.ev,
      'YearlyRollup'=yrly.ev,
      'MonthlyRollUp'=mth.ev,
      'DailyRollup'=dly.ev,
      'YearlyDiff'=(cns.ev-yrly.ev),
      'MonthlyDiff'=(cns.ev-mth.ev),
      'DailyDiff'=(cns.ev-dly.ev),
      'stat'=convert(char(20),null)
      from(select 'ev'=SUM(net)
          from table3 as a
            join table4 as pd on pd.STRG = a.productMappingKey
          where TrdDt >= @startDateYear
          and SalesGrpLvl1 = 'GIS'
          and reportDt >= @llystartDt
          and not(trddt >= @mtdmin and trddt <= @mtdmax and GPNum = '49135' and(trim(prod3) = 'Equities - Cash (EE)' or prod5 like '% DMA %' or prod5 like '% DSA %'))
          and not(gpnum = '63376' and trim(prod2) = 'Equity Cash' and trddt >= @mtdmin and trddt <= @mtdmax)) as cns
        ,(select 'ev'=SUM(ev)
          from table5 as r
            ,table6 as d
          where r.tradeDateMonthKey = d.month_key
          and d.year = @tradeYear
          and salesForce = 'GIS') as mth
        ,(select 'ev'=SUM(ev)
          from table7
          where tradeDate >= @startDateYear
          and salesForce = 'GIS') as dly
        ,(select 'ev'=SUM(ev)
          from table8
          where YEAR >= @tradeYear
          and salesForce = 'GIS'
          and dateType = 'tradeDate') as yrly 
          end

One thing that I noticed was that when I comment out Insert statement then the rest of the query works fine but when I uncomment it, I get the above mentioned error.

Answer 1

Please check your every query from table1 when assigning the value to a @variable.

For instance @cyear is expecting a scalar/single value but the "select * from table1 where last_trade_date = 'Y'" could have been returning multiple values.

You can only assign single value to a single @variable.