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

    Unanswered: IBM][CLI Driver][DB2/AIX64] SQL0811N The result of a scalar fullselect, SELECT INTO s

    I am getting following exception

    The exception is :: [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 :
    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

    please assist.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amitsingh379 View Post
    please assist.
    glad to


    Code:
    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
    all i am able to offer is a suggestion that you learn how to format a query to make it more readable

    as to the actual error message, one of those subqueries in the SELECT clause of the outer query is returning more than one row -- you'll have to figure out which one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought the query could be written like this...

    Code:
    SELECT projinfo.*
         , req.   tid
         , req.   fname||' '||req.   lname AS    reqname
         , engr.  fname||' '||engr.  lname AS   engrname
         , srengr.fname||' '||srengr.lname AS srengrname
         , sup.   fname||' '||sup.   lname AS    supname
         , req.   phone_num                AS    reqph
         , engr.  phone_num                AS   engrph 
         , srengr.phone_num                AS srengrph
         , sup.   phone_num                AS    supph
         , req.   email                    AS    reqemail
         , engr.  email                    AS   engremail
         , srengr.email                    AS srengremail
         , sup.   email                    AS    supemail
      FROM caepte.tbl_pte_projectinfo    projinfo
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile req
       ON  UPPER(req.   tid) = UPPER(projinfo.requestorid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile engr
       ON  UPPER(engr.  tid) = UPPER(projinfo.     engrid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile srengr
       ON  UPPER(srengr.tid) = UPPER(projinfo.   srengrid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile sup
       ON  UPPER(sup.   tid) = UPPER(projinfo.      supid)
     ORDER BY
           requestdate DESC

    as to the actual error message, one of those subqueries in the SELECT clause of the outer query is returning more than one row -- you'll have to figure out which one
    To find the cause of error, COUNT function is useful.
    Here is an example...
    Code:
    SELECT projinfo.requestorid
         , projinfo.     engrid
         , projinfo.   srengrid
         , projinfo.      supid
         , COUNT(req.   tid) AS    reqcount 
         , COUNT(engr.  tid) AS   engrcount 
         , COUNT(srengr.tid) AS srengrcount 
         , COUNT(sup.   tid) AS    supcount 
      FROM caepte.tbl_pte_projectinfo    projinfo
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile req
       ON  UPPER(req.   tid) = UPPER(projinfo.requestorid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile engr
       ON  UPPER(engr.  tid) = UPPER(projinfo.     engrid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile srengr
       ON  UPPER(srengr.tid) = UPPER(projinfo.   srengrid)
      LEFT OUTER JOIN
           caeedm.tbl_caeedm_userprofile sup
       ON  UPPER(sup.   tid) = UPPER(projinfo.      supid)
     GROUP BY
           projinfo.requestorid
         , projinfo.     engrid
         , projinfo.   srengrid
         , projinfo.      supid
    HAVING COUNT(req.   tid) > 1
       OR  COUNT(engr.  tid) > 1
       OR  COUNT(srengr.tid) > 1
       OR  COUNT(sup.   tid) > 1
     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
  •