Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > IBM][CLI Driver][DB2/AIX64] SQL0811N The result of a scalar fullselect, SELECT INTO s

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-03-10, 05:15
amitsingh379 amitsingh379 is offline
Registered User
  
Join Date: Apr 2010
Posts: 4
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.
Reply With Quote
  #2 (permalink)  
Old 05-03-10, 05:54
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 05-03-10, 15:46
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
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

Quote:
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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.