row_number() over partition by

Question

I have existing PL/SQL code with formatting for the client process that is doing a cursor with case for a join on multiple tables using row_number () over partition by. It was all fine, till we noticed that if a certain record had multiple contact information on it, the formatting the way it is set, instead of including all the contacts as part of the same record, it is outputting the contacts as different rows for the same customer. How do i tweak the cursor to make it appear as one record with all contact information(s)? Expected :

+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+
|  ID   |    NAME   |   CONTACT1 |       EMAIL1      |    PHONE1   |  CONTACT2 |       EMAIL2      |    PHONE2  |
+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+
| 50000 | Customer1 | Rodney     |  Rodney@gmail.com |  1112223333 |  Billy    | Billy@hotmail.com | 4445556666 |
+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+

Instead of :

+-------+------------+-----------+-------------------+-------------+
|  ID   |    NAME    |  CONTACT1 |       EMAIL1      |   PHONE1    |
+-------+------------+-----------+-------------------+-------------+
| 50000 |  Customer1 | Rodney    | Rodney@gmail.com  |  1112223333 |
| 50000 |  Customer1 | Billy     | Billy@hotmail.com | 4445556666  |
+-------+------------+-----------+-------------------+-------------+

The code is as follows :

cursor c1 is
       select case rn1 when 1 then "TypeOfContract" end "TypeOfContract",
       case rn1 when 1 then "ContractNumber" end "ContractNumber",
       case rn1 when 1 then "ClientName" end "ClientName",
       "AdminName",
       --case rn1 when 1 then "AdminName" end "AdminName",
       --case rn1 when 1 then "TechnicalName" end "TechnicalName",
       "TechnicalName",
       --case rn1 when 1 then "DayToDayName" end "DayToDayName",
       "DayToDayName",
       case rn2 when 1 then "ServiceName" end "ServiceName",
       case rn2 when 1 then "ServiceNumber" end "ServiceNumber",
       "SubserviceName",
       "SubserviceNumber",
       "Map",
       "VolumeOfFilesMessages",
       "VolumeOfPayments",
       "DollarAmountOfPayments"
from (select "TypeOfContract","ContractNumber","ClientName","AdminName","TechnicalName","DayToDayName",
        "ServiceName","ServiceNumber","SubserviceName","SubserviceNumber","Map","VolumeOfFilesMessages","VolumeOfPayments","DollarAmountOfPayments",
        row_number() over (partition by "TypeOfContract","ContractNumber","ClientName","AdminName","TechnicalName","DayToDayName"
        order by "ServiceName","ServiceNumber") rn1,
        row_number() over (partition by "TypeOfContract","ContractNumber","ClientName"/*,"AdminName","TechnicalName","DayToDayName"*/,"ServiceName","ServiceNumber"
        order by null) rn2
          from (
SELECT DISTINCT
case when tctc_cntipcli='C' then 'Host2Host' when tctc_cntipcli='L' then 'File Transfer Services' when tctc_cntipcli='I' then 'Integrated Payables' when tctc_cntipcli='D' then 'Data Exchange'
when tctc_cntipcli='V' then 'Vendor' when tctc_cntipcli='E' then 'External Bank'end as "TypeOfContract" ,
                   tctc_cncclipu as "ContractNumber",
                   regexp_replace(tctc_cndocidc, '[^[:alnum:]'' '']', NULL)  as "ClientName",
                   case when tcct_cncctto = 'A' then (select trim(tcct_cnctname)||trim(tcct_cnemail)||trim(tcct_cnphone) from dual) end as "AdminName"  ,
                  case when tcct_cncctto = 'T' then (select trim(tcct_cnctname)||trim(tcct_cnemail)||trim(tcct_cnphone) from dual) end as "TechnicalName",
                   case when tcct_cncctto = 'D' then (select trim(tcct_cnctname)||trim(tcct_cnemail)||trim(tcct_cnphone) from dual) end as "DayToDayName",
                   tsrv_cndesser as "ServiceName",
                   texe_cnfuncid as "ServiceNumber",
                   tsrs_cnsubsdc as "SubserviceName",
                   texe_cnsubser as "SubserviceNumber",
                   tmap_cndesc   as "Map"
                         from service.kndtctc, service.kndtexe, service.kndtscm, service.kndtsrv, service.kndtsrs, service.kndtmap, service.kndtcct
                          where tctc_cncclipu = texe_cncclipu
                          and texe_cnfuncid = tsrv_cncveser
                          and texe_cnfuncid = tsrs_cncveser
                          and texe_cnsubser = tsrs_cnsubser
                          and texe_cncclipu = tscm_cncontra
                          and tscm_cnmapco = tmap_cnmapco
                          and tscm_cnservic = tsrv_cncveser
                          and tscm_cnsubser = tsrs_cnsubser
                          and texe_cncclipu = tcct_cncclipu
                          and tscm_cncontra = tcct_cncclipu
                          and tctc_cnestado in ('01', '03')
                          and texe_cnestado in ('01', '03')
                          and tsrv_cnestado in ('01', '03')
                          and tsrs_cnestado in ('01', '03')
                          and tscm_cnestado in ('01', '03')
                          and tmap_cnestado in ('01', '03')
                          order by tctc_cncclipu
               ) 
       )
;

Appreciate any help the community can provide!

If there would be N records with same customer then should be N CONTACT ,EMAIL and PHONE columns? — Evgeniy K., Jul 07 '16 at 16:17
yes each customer could have 1, 2, or all three contact information(s). — QuickDrawMcgraw, Jul 07 '16 at 17:09
Then you can't do that without dynamic sql (see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547). Do you want dynamic example? — Evgeniy K., Jul 07 '16 at 17:13
yes please and thank you .. a customer can have multiple texe_cnfuncid so is there a way we can put the second contact information with that second row of output and the third contact information with that third row of output instead of all in the same row? — QuickDrawMcgraw, Jul 07 '16 at 17:18
@EvgeniyK you could sort of do it with out dynamic sql, Create a row number and then pivot based on the row number you would have to pick an upper limit of records and would end up with extra columns but it would do essentially what OP is asking — Matt, Jul 07 '16 at 17:56

score 0 · Answer 1 · answered Jul 07 '16 at 18:04

As you are in PL/SQL, it is possible to do this without dynamic SQL. You need to split your cursor into two. The first will return the main details you need. That is, this cursor will return one row for every physical row you need to see in the output. The second will return one row for every contact.

You then concatenate the details inside a nested loop. The rough shape of the code is as follows. It's a bit more code, but will get the effect you seek.

DECLARE

    CURSOR c_main
    IS
    SELECT ...

    CURSOR c_contact
    IS
    SELECT ...

    v_full_line  VARCHAR2(4000);

BEGIN

    FOR r_main IN c_main LOOP

        v_full_line := r_main.id || ' | ' || r_main.name || ' | ';

        FOR r_contact IN c_contact(r_main.id) LOOP
            v_full_line := v_full_line || r_contact.contact || ' | ' || r_contact.email || ' | ' || r_contact.phone || ' | ';
        END LOOP;

        -- Return v_full_line here...
    END LOOP;
END;
/

score 0 · Answer 2 · answered Jul 08 '16 at 05:39

Example with function(p_maxcol - maximum number of columns in your query. You can simply get it just run max(count(*)) on your query).

CREATE OR REPLACE FUNCTION get_allitems(p_maxcol number)
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
  res varchar2(32767);
  -- type in this variable your query.
  query varchar2(32767) := q'[(select  50000 as id ,  'Customer1' as NAME, 'Rodney' as CONTACT  , 'Rodney@gmail.com' as EMAIL,  1112223333 as  PHONE   from dual union all
                              select 50000 ,  'Customer1' , 'Billy'    , 'Billy@hotmail.com' , 4445556666  from dual union all
                              select 60000 ,  'Customer2' , 'Garry'    , 'Garry@hotmail.com' , 1232356666  from dual)]';
BEGIN
  res := q'{select id, EXTRACTVALUE(xml,'/PivotSet/item[1]/column[4]') as NAME}';
  for i in 1..p_maxcol loop
    res := res || q'{,EXTRACTVALUE(xml,'/PivotSet/item[}' || i || q'{]/column[1]') as CONTACT }' ||
                  q'{,EXTRACTVALUE(xml,'/PivotSet/item[}' || i || q'{]/column[2]') as EMAIL }' ||
                  q'{,EXTRACTVALUE(xml,'/PivotSet/item[}' || i || q'{]/column[3]') as PHONE }';
  end loop;
  res := res || 'from (select id, contact_email_phone_xml as xml from ' || query || ' pivot xml (max(NAME) as d for (CONTACT,EMAIL,PHONE) in(any,any,any)))';
  OPEN my_cursor FOR res;
  RETURN my_cursor;
END get_allitems;

Usage

select get_allitems(2) from dual

Also take note that the maximum number of column in Oracle is 255.

In static sql more relative example to you(with concatinating columns) is this(Note. Max column length is 4000):

with t( ID , NAME  ,  CONTACT , EMAIL , PHONE) as(
select  50000 ,  'Customer1' , 'Rodney'   , 'Rodney@gmail.com' ,  1112223333  from dual union all
select 50000 ,  'Customer1' , 'Billy'    , 'Billy@hotmail.com' , 4445556666  from dual union all
select 60000 ,  'Customer2' , 'Garry'    , 'Garry@hotmail.com' , 1232356666  from dual)
select id, name, listagg(CONTACT || '|' || EMAIL || '|' || PHONE || '|') within group (order by CONTACT) from t
 group by  id, name

row_number() over partition by

2 Answers2