title: Scalar Fullselect issue in IBM DB2 version 9
body: <p>I have structered a query as below, the resultset of which is returned accurately when a limited set of inputs are fed in, viz., when an input of 500 records are fed, the query results with a scalar fullselect error message as below.</p>
  
  <pre><code> "Error: SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.  SQLSTATE=21000 (State:21000, Native Code: FFFFFCD5)"
  </code></pre>
  
  <p>But, when the same set of records are split up and fed viz., to say by feeding the first 100 records and then the next 100 and so on until all the 500 records are fed, then the query works fine without any error message. The query I have formulated is as below.</p>
  
  <pre><code>  select  d.pol_id,
      (select rtrim(a.CLI_INDV_TITL_TXT)||','||
              rtrim(a.ENTR_SUR_NM)||','||
              rtrim(a.CLI_INDV_MID_NM)||','||
              rtrim(a.ENTR_GIV_NM)
       from   uding604.tclnm a
       where  a.co_id = 'CP'
       and    a.cli_id = d.cli_id) "Client Name",
      (select RTRIM(a.CLI_ADDR_LN_1_TXT)||','||
              RTRIM(a.CLI_ADDR_LN_2_TXT)||','||
              RTRIM(a.CLI_ADDR_LN_3_TXT)||','||
              RTRIM(a.CLI_ADDR_MUN_CD)||','||
              RTRIM(a.CLI_ADDR_CNTY_CD)||','||
              RTRIM(a.CLI_CITY_NM_TXT)||','||(select  rtrim(etbl.etbl_desc_txt)
                                              from    uding604.tedit etbl
                                              where   etbl.co_id = 'CP'
                                              and     etbl.etbl_typ_id = 'STACD'
                                              and     etbl.etbl_valu_id = RTRIM(a.CLI_CRNT_LOC_CD))||','||
              (case when rtrim(a.CLI_CTRY_CD) = 'IN'
                    then 'India'
                    else '' end)||','||
              RTRIM(a.CLI_PSTL_CD)
       from   uding604.tclia a
       where  a.co_id = 'CP'
       and    a.cli_addr_typ_cd = 'PR'
       and    a.cli_id = d.cli_id) "Client Address",
       coalesce((select  RTRIM(a.CLI_CNTCT_ID_TXT)
                 from    uding604.tclic a
                 where   a.co_id = 'CP'
                 and     a.CLI_CNTCT_ID_CD = 'CP'
                 and     a.cli_id = d.cli_id),'') "Client Mobile",
       coalesce((select  RTRIM(a.CLI_CNTCT_ID_TXT)
                 from    uding604.tclic a
                 where   a.co_id = 'CP'
                 and     a.CLI_CNTCT_ID_CD = 'EM'
                 and     a.cli_id = d.cli_id),'') "Client Email",
      (select pol.serv_agt_id
       from   uding604.tpol pol
       where  pol.co_id = 'CP'
       and    pol.pol_id = d.pol_id
       and    pol.serv_agt_id in (select      agt_id
                                  from        uding604.tag
                                  where       co_id = 'CP'
                                  and         agt_stat_cd = 'A')
       fetch first 1 row only) "Agent ID",
      (select case
              when    ag.agt_stat_cd = 'A'
              then    'ACTIVE'
              else    ''
              end
       from   uding604.tag ag
       where  ag.co_id = 'CP'
       and    ag.agt_id in (select    pol.serv_agt_id
                            from      uding604.tpol pol
                            where     pol.co_id = 'CP'
                            and       pol.pol_id = d.pol_id)) "Agent Status",
      (select rtrim(a.CLI_INDV_TITL_TXT)||' '||
              rtrim(a.ENTR_SUR_NM)||' '||
              rtrim(a.CLI_INDV_MID_NM)||' '||
              rtrim(a.ENTR_GIV_NM)
       from   uding604.tclnm a
       where  a.co_id = 'CP'
       and    a.cli_id in (select     pol.serv_agt_id
                           from       uding604.tpol pol
                           where      pol.co_id = 'CP'
                           and        pol.pol_id = d.pol_id
                           and        pol.serv_agt_id in (select      agt_id
                                                          from        uding604.tag
                                                          where       co_id = 'CP'
                                                          and         agt_stat_cd = 'A')
                                                          fetch first 1 row only)) "Advisor Name",
      (coalesce((select RTRIM(a.CLI_CNTCT_ID_TXT)
                 from   uding604.tclic a
                 where  a.co_id = 'CP'
                 and    a.CLI_CNTCT_ID_CD = 'CP'
                 and    a.cli_id = (select pol.serv_agt_id
                                    from   uding604.tpol pol
                                    where  pol.co_id = 'CP'
                                    and    pol.pol_id = d.pol_id
                                    and    pol.serv_agt_id in (select agt_id
                                                               from   uding604.tag
                                                               where  co_id = 'CP'
                                                               and    agt_stat_cd = 'A'))),'')) "Agent Mobile",
      (coalesce((select RTRIM(a.CLI_CNTCT_ID_TXT)
                 from   uding604.tclic a
                 where  a.co_id = 'CP'
                 and    a.CLI_CNTCT_ID_CD = 'EM'
                 and    a.cli_id = (select    pol.serv_agt_id
                                    from      uding604.tpol pol
                                    where     pol.co_id = 'CP'
                                    and       pol.pol_id = d.pol_id
                                    and       pol.serv_agt_id in (select  agt_id
                                                                  from    uding604.tag
                                                                  where   co_id = 'CP'
                                                                  and     agt_stat_cd = 'A'))),''))"Agent Email",
      (select etbl.etbl_desc_txt
       from   uding604.tedit etbl
       where  etbl.co_id = 'CP'
       and    etbl.etbl_typ_id = 'BRIND'
       and    etbl.etbl_valu_id in (select br_id
                                    from   uding604.tag
                                    where  co_id = 'CP'
                                    and    agt_stat_cd = 'A'
                                    and    agt_id in (select       serv_agt_id
                                                      from         uding604.tpol m
                                                      where        m.co_id = 'CP'
                                                      and          m.pol_id = d.pol_id))) "Advisor Branch Id"
  from    uding604.tpolc d
  where   d.co_id = 'CP'
  and     d.cli_id in (select pol_id from uding604.polid_tmp)  --this subquery holds the records to be provided as input and currently the 'uding604.polid_tmp' table in the subquery holds 500 records
  and     d.pol_id in (select     pol_id
                       from       uding604.tpol
                       where      co_id = 'CP'
                       and        pol_cstat_Cd in ('1','4')
                       and        pol_iss_eff_dt between '2008-09-26' and '2010-09-26')
  and     d.pol_id in (select max(a.pol_id)
                       from   uding604.tpolc a
                       where  a.co_id = 'CP'
                       and    a.cli_id = d.cli_id)
  and     d.pol_cli_rel_typ_cd = 'O'
  </code></pre>
  
  <p>I feel that I am very close to a solution, but what is it?</p>