Using select in select in oracle report query

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 (s开发者_Python百科um()).

Please urgent help needed.

Thanks.

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 ...

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?

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

Which version of Oracle Reports ?

The scalar subqueries syntax came in around Oracle 8/8i, but the development of Oracle reports stopped sometime around the stone-age. A lot of the enhanced SQL syntax of the last 10+ years hasn't made it in.

If you can create the bulk of the query as a view in the database, and just select from the view in the report, then you have more flexibility