Oracle: Single Row Subquery Returns More than One Row

Question

Based on other questions I believe I need a correlated sub query but I can't quite figure out the syntax. My sub selection creates multiple rows per jobnumber.

select distinct projectmaster.projectidentity as PROJECT,
                jdedwards.jobnumber,

                (select cast(substr(overagenarrative, 1, 2000) as
                             varchar(4000 byte))
                   from jlog.approvedjobsoverbudget
                   LEFT OUTER JOIN jlog.jdedwards
                     ON jdedwards.jobnumber =
                        approvedjobsoverbudget.jobnumber) as Overage

  from jlog.jdedwards,
       jlog.projectjobdetail,
       jlog.projectmaster,
       jlog.approvedjobsoverbudget
 where jdedwards.jobnumber = projectjobdetail.jobnumber
   and projectjobdetail.projectidentity = projectmaster.projectidentity
   and jdedwards.budgetyear = projectmaster.budgetyear
   and jlog.projectmaster.Projectstatus in (2)

   and jdedwards.jobnumber = approvedjobsoverbudget.jobnumber

Answer 1

Why not simply

select distinct projectmaster.projectidentity as PROJECT,
                jdedwards.jobnumber,
                --
-- this line ...
                cast(substr(overagenarrative, 1, 2000) as varchar2(4000 byte) as overage
-- ... instead of all this:
--                (select cast(substr(overagenarrative, 1, 2000) as
--                             varchar(4000 byte))
--                   from jlog.approvedjobsoverbudget
--                   LEFT OUTER JOIN jlog.jdedwards
--                     ON jdedwards.jobnumber =
--                        approvedjobsoverbudget.jobnumber) as Overage
  from jlog.jdedwards,
       jlog.projectjobdetail,
       jlog.projectmaster,
       jlog.approvedjobsoverbudget
 where jdedwards.jobnumber = projectjobdetail.jobnumber
   and projectjobdetail.projectidentity = projectmaster.projectidentity
   and jdedwards.budgetyear = projectmaster.budgetyear
   and jlog.projectmaster.Projectstatus in (2)
   and jdedwards.jobnumber = approvedjobsoverbudget.jobnumber

Note comments I put into the code.