Oracle: Single Row Subquery Returns More than One Row

50 Views Asked by At

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
1

There are 1 best solutions below

1
On BEST ANSWER

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.