Scalar Fullselect issue in IBM DB2 version 9_问答_开发者

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.

 "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)"

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.

  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'
                      开发者_如何学C            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'

I feel that I am very close to a solution, but what is it?

Because when you do something like a.cli_id = (select pol.serv_agt_id the part to the right of the equal sign can only return one row.

So when you add the additional data, going from 100 to 500 rows, there is something in the additional data that causes those subqueries to return more than one record. You can do in instead of = or else check your data for duplicates or add additional qualifiers in your where clause to filter out the unwanted records.