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 getting the following Exception

    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

    Here is my query:
    with RPL (TID,FNAME,LNAME,rec_cnt) AS (SELECT ROOT.TID,ROOT.FNAME,ROOT.LNAME,1 FROM CAEEDM.TBL_CAEEDM_USERPROFILE ROOT WHERE upper(ROOT.TID) = upper('T3098JS') UNION ALL SELECT CHILD.TID,CHILD.FNAME,CHILD.LNAME,parent.rec_cnt + 1 FROM RPL PARENT, CAEEDM.TBL_CAEEDM_USERPROFILE CHILD WHERE PARENT.TID = CHILD.BOSSID AND parent.rec_cnt < 100)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) where( (upper(requestorid) in (select upper(tid) from rpl)) AND( ( days(current date) - days(date(CAEPTE.TBL_PTE_PROJECTINFO.CANCELARCHIVE DATE)) <8 AND CAEPTE.TBL_PTE_PROJECTINFO.STATUS=90) OR (days(current date) - days(date(CAEPTE.TBL_PTE_PROJECTINFO.CANCELARCHIVE DATE)) < 8 AND CAEPTE.TBL_PTE_PROJECTINFO.STATUS=80) OR (CAEPTE.TBL_PTE_PROJECTINFO.STATUS!=80 AND CAEPTE.TBL_PTE_PROJECTINFO.STATUS!=90))) order by RequestDate DESC

    Please assist.
    thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Change all "FNAME||' '||LNAME", "PHONE_NUM" and "EMAIL" in your subselects to "COUNT(*)".
    Then you will find subselect(s) which returned more than one row.
    Last edited by tonkuma; 07-27-10 at 09:12.

Posting Permissions

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