Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    4

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

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •