row_number() по разделу на

У меня есть существующий код PL/SQL с форматированием для клиентского процесса, который выполняет курсор с регистром для объединения нескольких таблиц с использованием row_number() над разделом. Все было в порядке, пока мы не заметили, что если в определенной записи содержится несколько контактных данных, форматирование, установленное таким образом, вместо включения всех контактов в одну и ту же запись выводит контакты в виде разных строк для тот же клиент. Как настроить курсор, чтобы он отображался как одна запись со всей контактной информацией? Ожидал :

+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+
|  ID   |    NAME   |   CONTACT1 |       EMAIL1      |    PHONE1   |  CONTACT2 |       EMAIL2      |    PHONE2  |
+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+
| 50000 | Customer1 | Rodney     |  [email protected] |  1112223333 |  Billy    | [email protected] | 4445556666 |
+-------+-----------+------------+-------------------+-------------+-----------+-------------------+------------+

Вместо :

+-------+------------+-----------+-------------------+-------------+
|  ID   |    NAME    |  CONTACT1 |       EMAIL1      |   PHONE1    |
+-------+------------+-----------+-------------------+-------------+
| 50000 |  Customer1 | Rodney    | [email protected]  |  1112223333 |
| 50000 |  Customer1 | Billy     | [email protected] | 4445556666  |
+-------+------------+-----------+-------------------+-------------+

Код выглядит следующим образом:

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
               ) 
       )
;

Цените любую помощь, которую может предоставить сообщество!


sql oracle partition plsql cursor
person QuickDrawMcgraw    schedule 07.07.2016    source источник
comment
Если будет N записей с одним и тем же клиентом, тогда должно быть N столбцов CONTACT, EMAIL и PHONE?   -  person Evgeniy K.    schedule 07.07.2016
comment
да, у каждого клиента может быть 1, 2 или все три контактные данные.   -  person QuickDrawMcgraw    schedule 07.07.2016
comment
Тогда вы не сможете сделать это без динамического sql (см. "nofollow noreferrer">asktom.oracle.com/pls/apex/). Хотите динамический пример?   -  person Evgeniy K.    schedule 07.07.2016
comment
да, пожалуйста, и спасибо .. клиент может иметь несколько texe_cnfuncid, так есть ли способ, которым мы можем поместить вторую контактную информацию с этой второй строкой вывода и третью контактную информацию с этой третьей строкой вывода, а не все в одной строке?   -  person QuickDrawMcgraw    schedule 07.07.2016
comment
@EvgeniyK, вы могли бы сделать это без динамического sql, создать номер строки, а затем выполнить поворот на основе номера строки, вам нужно было бы выбрать верхний предел записей и в конечном итоге получить дополнительные столбцы, но по сути это было бы то, что OP спрашивая   -  person Matt    schedule 07.07.2016
comment
матовый пример, пожалуйста?   -  person QuickDrawMcgraw    schedule 07.07.2016

Ответы (2)


arrow_upward
0
arrow_downward

Поскольку вы используете PL/SQL, это можно сделать без динамического SQL. Вам нужно разделить курсор на две части. Первый вернет основные детали, которые вам нужны. То есть этот курсор будет возвращать одну строку для каждой физической строки, которую вам нужно увидеть в выводе. Второй вернет одну строку для каждого контакта.

Затем вы объединяете детали внутри вложенного цикла. Грубая форма кода выглядит следующим образом. Это немного больше кода, но вы получите эффект, который вы ищете.

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;
/
person AdamRossWalker    schedule 07.07.2016

arrow_upward
0
arrow_downward

Пример с функцией (p_maxcol - максимальное количество столбцов в вашем запросе. Вы можете просто получить его, просто запустив max(count(*)) в своем запросе).

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  , '[email protected]' as EMAIL,  1112223333 as  PHONE   from dual union all
                              select 50000 ,  'Customer1' , 'Billy'    , '[email protected]' , 4445556666  from dual union all
                              select 60000 ,  'Customer2' , 'Garry'    , '[email protected]' , 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;

Применение

select get_allitems(2) from dual

Также обратите внимание, что максимальное количество столбцов в Oracle равно 255.

В статическом sql более относительный пример для вас (с конкатенацией столбцов) таков (Примечание. Максимальная длина столбца составляет 4000):

with t( ID , NAME  ,  CONTACT , EMAIL , PHONE) as(
select  50000 ,  'Customer1' , 'Rodney'   , '[email protected]' ,  1112223333  from dual union all
select 50000 ,  'Customer1' , 'Billy'    , '[email protected]' , 4445556666  from dual union all
select 60000 ,  'Customer2' , 'Garry'    , '[email protected]' , 1232356666  from dual)
select id, name, listagg(CONTACT || '|' || EMAIL || '|' || PHONE || '|') within group (order by CONTACT) from t
 group by  id, name
person Evgeniy K.    schedule 08.07.2016