COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/AIX64] SQL0811N The result of a

amitsingh379 · #1 (**permalink**) 04-22-10, 03:43

I am new to DB2
I am getting the following error.

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/AIX64] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

My query is as follows:

select CAEPTE.TBL_PTE_PROJECTINFO.*, CAEEDM.TBL_CAEEDM_USERPROFILE.tid ,
(select FNAME||' '||LNAME from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.requestorid)) as REQname,(select FNAME||' '||LNAME from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.ENGRID)) as ENGRname,(select FNAME||' '||LNAME from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SRENGRID) ) as SRENGRname,(select FNAME||' '||LNAME from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SUPID) ) as SUPname,(select PHONE_NUM from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.requestorid) ) as REQPh,(select PHONE_NUM from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.ENGRID) ) as ENGRPh,(select PHONE_NUM from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SRENGRID) ) as SRENGRPh,(select PHONE_NUM from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SUPID) ) as SUPPh, (select EMAIL from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.requestorid) ) as REQEmail,(select EMAIL from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.ENGRID) ) as ENGREmail,(select EMAIL from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SRENGRID) ) as SRENGREmail,(select EMAIL from CAEEDM.TBL_CAEEDM_USERPROFILE where upper(tid) = upper(CAEPTE.TBL_PTE_PROJECTINFO.SUPID) ) as SUPEmail from CAEPTE.TBL_PTE_PROJECTINFO left outer join CAEEDM.TBL_CAEEDM_USERPROFILE on upper(CAEPTE.TBL_PTE_PROJECTINFO.requestorid) = upper(CAEEDM.TBL_CAEEDM_USERPROFILE.tid) order by RequestDate DESC

Thanks in advance.

tonkuma · #2 (**permalink**) 04-22-10, 05:13

An idea to find the cause of the error would be replacing the results of scalar subselects with COUNT(*),
like this...

Code:

select a.*
     , b.tid
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.requestorid)
       ) as REQname
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.ENGRID)
       ) as ENGRname
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SRENGRID)
       ) as SRENGRname
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SUPID)
       ) as SUPname
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.requestorid)
       ) as REQPh
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.ENGRID)
       ) as ENGRPh
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SRENGRID)
       ) as SRENGRPh
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SUPID)
       ) as SUPPh
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.requestorid)
       ) as REQEmail
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.ENGRID)
       ) as ENGREmail
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SRENGRID)
       ) as SRENGREmail
     , (select COUNT(*)
          from CAEEDM.TBL_CAEEDM_USERPROFILE
         where upper(tid) = upper(a.SUPID)
       ) as SUPEmail
  from CAEPTE.TBL_PTE_PROJECTINFO    a
  left outer join
       CAEEDM.TBL_CAEEDM_USERPROFILE b
   on  upper(a.requestorid) = upper(b.tid)
 order by
       RequestDate DESC

Internet Services

Web Development

Digital Marketing

Consumer Tech