Oracle:单行子查询返回多行

Question

基于其他问题，我认为我需要一个相关子查询，但我不太清楚其语法。我的子选择为每个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

为什么不简单地

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

注意我放入代码中的注释。