在oracle报表查询中使用select in select

发布于 2024-10-22 04:31:36 字数 704 浏览 16 评论 0原文

我正在创建一份新报告，其中报告中只有一个光标，并且该报告没有纸张布局（它直接转为 CSV 格式）。

我有一个查询，我需要进行子查询，例如

SELECT
         grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ....
         (((SELECT SUM(nett_instalment_invoice_amount) 
            FROM instalments
            WHERE member_product_id = member_product_id)) subscription)
FROM

...... 等等......

在编译报告时，我遇到一个错误，提示

Encountered Symbol SELECT while expecting one of the following symbols:
(  +  -

如果不在最后添加 group by 子句，这是否可以得到帮助？

当我尝试另一种方法时，放入 group by 子句并直接使用 sum 函数而不是 select (sum())。

请紧急需要帮助。

谢谢。

原文

I am creating a new report where I have only one cursor in the report and the report has no paper layout (it goes directly to a CSV format).

I have a query where I need to make a sub query something like

SELECT
         grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ....
         (((SELECT SUM(nett_instalment_invoice_amount) 
            FROM instalments
            WHERE member_product_id = member_product_id)) subscription)
FROM

....
and so on....

While compiling report I encounter an error saying

Encountered Symbol SELECT while expecting one of the following symbols:
(  +  -

can this be helped without adding a group by clause at the end?

As I tried an alternative approach by putting in a group by clause and directly using sum function instead of select (sum()).

Please urgent help needed.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一页 2024-10-29 04:31:36

有一个简单的解决方法，通过创建一个函数，让我们将其称为 get_installment_inv_sum ，并带有一个参数 member_product_id ，如下所示：

create or replace function get_installment_inv_sum (p_member_product_id number) return number
as 
 v_sum number
begin
 SELECT SUM(nett_instalment_invoice_amount) 
            into v_sum
            FROM instalments
            WHERE member_product_id = p_member_product_id;
 return v_sum;
end;

然后您可以像这样调用您的选择：

SELECT
         grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ....
         ,
         get_installment_inv_sum(member_product_id) subscription
FROM ...

there is a simple workaround for this by creating a function let's call it get_installment_inv_sum with one parameter member_product_id like this:

create or replace function get_installment_inv_sum (p_member_product_id number) return number
as 
 v_sum number
begin
 SELECT SUM(nett_instalment_invoice_amount) 
            into v_sum
            FROM instalments
            WHERE member_product_id = p_member_product_id;
 return v_sum;
end;

then you can call your select like:

SELECT
         grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ....
         ,
         get_installment_inv_sum(member_product_id) subscription
FROM ...

回复收藏 0 原文

两相知 2024-10-29 04:31:36

这当然是允许的：

select d.dname, (sum(e.sal) from emp e where e.deptno = d.deptno) as dept_sal
from dept d;

那么这也是：

select d.dname, sum(e.sal)
from dept d
left outer join emp e on e.deptno = d.deptno
group by d.dname;

那么你到底做了什么？

This is certainly allowed:

select d.dname, (sum(e.sal) from emp e where e.deptno = d.deptno) as dept_sal
from dept d;

And so is this:

select d.dname, sum(e.sal)
from dept d
left outer join emp e on e.deptno = d.deptno
group by d.dname;

So what did you do exactly?

回复收藏 0 原文

天暗了我发光 2024-10-29 04:31:36

SELECT /*+ ORDERED PUSH_SUBQ*/
          11
         ,'SQ'
         ,grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ,intm.intermediary_id                     intermediary_id          --R3352 AUS Regulatory added new field
         ,intm.intermediary_name                   intermediary_name 
         ,regi.registration_id                     registration_id
         ,mere.member_id                           member_id
         ,memb.title_code                          title_code
         ,memb.given_name||' '||memb.family_name   member_name
         --,mech.birth_date                          birth_date
         ,mere.company_employee_ref                company_employee_ref
         ,memb.orig_risk_start_date                orig_risk_start_date
         ,mere.original_bi_joining_date            orig_joining_date
         ,mepr.member_product_id                   member_product_id
         ,mepr.member_product_risk_start_date      mp_risk_start_date
         ,mepr.product_id                          product_id
         ,INITCAP(prod.product_name)               product_name
         ,cont.currency_code
         ,mere.customer_status_code
         /* ,((SELECT SUM(inst.nett_instalment_invoice_amount) 
            FROM instalments inst
            WHERE inst.member_product_id = mepr.member_product_id)) subscription */
         ,SUM(inst.nett_instalment_invoice_amount) subscription
  FROM    registrations                 regi
         ,member_registrations          mere
         ,member_products               mepr
         ,contracts                     cont
         ,products                      prod
         ,members                       memb
         ,groups                        grou
         ,intermediaries                intm
         ,insurers                      insu        
         ,instalments                   inst
  WHERE  insu.insurer_id                = i_insurerid
  AND    NVL(i_reportdate  ,Sysdate)    >= cont.contract_risk_start_date
  AND   NVL(i_reportdate  ,Sysdate)     < cont.renewal_date      
  --AND    regi.GROUP_ID                   IN (77648,77658) --Arv
  AND    prod.insurer_id                 = insu.insurer_id(+)
  AND    mere.member_id                  = memb.member_id
  AND    mere.member_id                  = mepr.member_id
  AND    mepr.contract_pk                = cont.contract_pk
  AND    mepr.product_id                 = prod.product_id(+)
  AND    mepr.intermediary_id            = intm.intermediary_id(+)
  AND    mere.registration_id            = regi.registration_id 
  AND    mere.registration_id            = mepr.registration_id
  AND    regi.GROUP_ID                   = grou.group_id 
  AND    inst.member_product_id          = mepr.member_product_id
  AND    grou.group_level_code       IN ('G','R')
  AND    mere.customer_status_code       IN ('A','L')      
  AND    family_name_uppercase          <> UPPER('zz** Please Ignore This Member **zz') 
  AND    given_name_uppercase           <> UPPER('zz** Please Ignore **zz') --l_given_name_uppercase

    AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  GROUP_ID               = regi.GROUP_ID
           AND    suspend_lapse_ind      = 'L'
           AND    reinstatement_ind      = 'N'
           AND    lapse_effective_date  <= NVL(i_reportdate  ,Sysdate)
           UNION
           SELECT 'X'
           FROM   customer_lapses
           WHERE  registration_id        = regi.registration_id
           AND    suspend_lapse_ind      = 'L'
           AND    reinstatement_ind      = 'N'
           AND    lapse_effective_date  <= NVL(i_reportdate  ,Sysdate)
         )

  AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  mem_reg_member_id        = mere.member_id
           AND    mem_reg_registration_id  = mere.registration_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
         )

  AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  member_product_id        = mepr.member_product_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
           UNION
           SELECT 'X'
           FROM   customer_lapses
           WHERE  cust_prod_contract_pk    = mepr.contract_pk
           AND    cust_prod_product_id     = mepr.product_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
         )

         Group BY    11
         ,'SQ'
         ,grou.GROUP_ID                           -- GROUP_ID
         ,grou.group_name                         -- group_name
         ,intm.intermediary_id                    -- intermediary_id          --R3352 AUS Regulatory added new field
         ,intm.intermediary_name                  -- intermediary_name 
         ,regi.registration_id                  --   registration_id
         ,mere.member_id                         --  member_id
         ,memb.title_code                        --  title_code
         ,memb.given_name||' '||memb.family_name  -- member_name
         --,mech.birth_date                          birth_date
         ,mere.company_employee_ref               -- company_employee_ref
         ,memb.orig_risk_start_date              --  orig_risk_start_date
         ,mere.original_bi_joining_date          --  orig_joining_date
         ,mepr.member_product_id                 --  member_product_id
         ,mepr.member_product_risk_start_date     -- mp_risk_start_date
         ,mepr.product_id                         -- product_id
         ,INITCAP(prod.product_name)              -- product_name
         ,cont.currency_code
         ,mere.customer_status_code

SELECT /*+ ORDERED PUSH_SUBQ*/
          11
         ,'SQ'
         ,grou.GROUP_ID                            GROUP_ID
         ,grou.group_name                          group_name
         ,intm.intermediary_id                     intermediary_id          --R3352 AUS Regulatory added new field
         ,intm.intermediary_name                   intermediary_name 
         ,regi.registration_id                     registration_id
         ,mere.member_id                           member_id
         ,memb.title_code                          title_code
         ,memb.given_name||' '||memb.family_name   member_name
         --,mech.birth_date                          birth_date
         ,mere.company_employee_ref                company_employee_ref
         ,memb.orig_risk_start_date                orig_risk_start_date
         ,mere.original_bi_joining_date            orig_joining_date
         ,mepr.member_product_id                   member_product_id
         ,mepr.member_product_risk_start_date      mp_risk_start_date
         ,mepr.product_id                          product_id
         ,INITCAP(prod.product_name)               product_name
         ,cont.currency_code
         ,mere.customer_status_code
         /* ,((SELECT SUM(inst.nett_instalment_invoice_amount) 
            FROM instalments inst
            WHERE inst.member_product_id = mepr.member_product_id)) subscription */
         ,SUM(inst.nett_instalment_invoice_amount) subscription
  FROM    registrations                 regi
         ,member_registrations          mere
         ,member_products               mepr
         ,contracts                     cont
         ,products                      prod
         ,members                       memb
         ,groups                        grou
         ,intermediaries                intm
         ,insurers                      insu        
         ,instalments                   inst
  WHERE  insu.insurer_id                = i_insurerid
  AND    NVL(i_reportdate  ,Sysdate)    >= cont.contract_risk_start_date
  AND   NVL(i_reportdate  ,Sysdate)     < cont.renewal_date      
  --AND    regi.GROUP_ID                   IN (77648,77658) --Arv
  AND    prod.insurer_id                 = insu.insurer_id(+)
  AND    mere.member_id                  = memb.member_id
  AND    mere.member_id                  = mepr.member_id
  AND    mepr.contract_pk                = cont.contract_pk
  AND    mepr.product_id                 = prod.product_id(+)
  AND    mepr.intermediary_id            = intm.intermediary_id(+)
  AND    mere.registration_id            = regi.registration_id 
  AND    mere.registration_id            = mepr.registration_id
  AND    regi.GROUP_ID                   = grou.group_id 
  AND    inst.member_product_id          = mepr.member_product_id
  AND    grou.group_level_code       IN ('G','R')
  AND    mere.customer_status_code       IN ('A','L')      
  AND    family_name_uppercase          <> UPPER('zz** Please Ignore This Member **zz') 
  AND    given_name_uppercase           <> UPPER('zz** Please Ignore **zz') --l_given_name_uppercase

    AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  GROUP_ID               = regi.GROUP_ID
           AND    suspend_lapse_ind      = 'L'
           AND    reinstatement_ind      = 'N'
           AND    lapse_effective_date  <= NVL(i_reportdate  ,Sysdate)
           UNION
           SELECT 'X'
           FROM   customer_lapses
           WHERE  registration_id        = regi.registration_id
           AND    suspend_lapse_ind      = 'L'
           AND    reinstatement_ind      = 'N'
           AND    lapse_effective_date  <= NVL(i_reportdate  ,Sysdate)
         )

  AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  mem_reg_member_id        = mere.member_id
           AND    mem_reg_registration_id  = mere.registration_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
         )

  AND    NOT EXISTS
         ( SELECT 'X'
           FROM   customer_lapses
           WHERE  member_product_id        = mepr.member_product_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
           UNION
           SELECT 'X'
           FROM   customer_lapses
           WHERE  cust_prod_contract_pk    = mepr.contract_pk
           AND    cust_prod_product_id     = mepr.product_id
           AND    suspend_lapse_ind        = 'L'
           AND    reinstatement_ind        = 'N'
           AND    lapse_effective_date    <= NVL(i_reportdate  ,Sysdate)
         )

         Group BY    11
         ,'SQ'
         ,grou.GROUP_ID                           -- GROUP_ID
         ,grou.group_name                         -- group_name
         ,intm.intermediary_id                    -- intermediary_id          --R3352 AUS Regulatory added new field
         ,intm.intermediary_name                  -- intermediary_name 
         ,regi.registration_id                  --   registration_id
         ,mere.member_id                         --  member_id
         ,memb.title_code                        --  title_code
         ,memb.given_name||' '||memb.family_name  -- member_name
         --,mech.birth_date                          birth_date
         ,mere.company_employee_ref               -- company_employee_ref
         ,memb.orig_risk_start_date              --  orig_risk_start_date
         ,mere.original_bi_joining_date          --  orig_joining_date
         ,mepr.member_product_id                 --  member_product_id
         ,mepr.member_product_risk_start_date     -- mp_risk_start_date
         ,mepr.product_id                         -- product_id
         ,INITCAP(prod.product_name)              -- product_name
         ,cont.currency_code
         ,mere.customer_status_code

回复收藏 0 原文