query which returns 2 or more results

Hi Experts, I attach a query which returns 2 or more results, can help me modify it to return the largest fecha_acuerdo
image001.png
query.txt
enrique_aeoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
awking00Connect With a Mentor Commented:
Modify your query to include a row number, then use that as a subquery to select from where the row number = 1 -

select x.col1,x.col2,..., x.coln from
(select ...,
row_number() over (partition by nro_telefono order by fecha_acuerdo desc) as rn
from ...) x
where x.rn = 1;

Note - Providing an alias of x for the subquery works in Oracle. In SQL Server, I think you need to add the AS keyword.
0
 
NikolasGConnect With a Mentor Commented:
Hi,
I would suggest a max(fecha_acuerdo) over a group by for all the identical rows
or a sub query that would bring the id of the record with the largest value on the row that you want..
We would be more help full if you provided the question that you are trying to run.
hope it helps.
Please feed back.

Since you added the query you could try the following

SELECT /*+DRIVING_SITE(ch)*/ /*+DRIVING_SITE(co)*/ /*+DRIVING_SITE(cu)*/ /*+DRIVING_SITE(cc)*/ /*+DRIVING_SITE(rp)*/ /*+DRIVING_SITE(icc)*/ /*+DRIVING_SITE(dt)*/
 dt.dn_num NRO_TELEFONO,
 dt.co_id,
 ch.ch_validfrom FECHA_ACUERDO,
 dt.customer_id,
 cu.custcode,
 cu.cscompregno RUC_DNI,
 cc.ccname RAZON_SOCIAL,
 cc.ccaddr1 || ' ' || cc.ccaddr3 || ' ' || cc.ccstreet || ' ' || cc.cccity DIRECCION,
 cu.prgcode TIPO_CLIENTE,
 dt.tmcode CODIGO_PLAN,
 decode(substr(rp.shdes, 0, 3), 'CON', 'C', 'P') TIPO_PLAN,
 'A' ESTADO_LINEA,
 --F_OBTENER_CF(dt.tmcode) CARGO_FIJO,
 --F_OBTENER_DEVIATING(dt.co_id, dt.tmcode) DEVIATING,
 decode(cu.cscusttype,
        'C',
        nvl(icc.combo14, icc.combo06),
        'B',
        nvl(icc.combo06, icc.combo14),
        'LIBRE') ACUERDO,
 CH.CH_REASON reason_id,
 (SELECT A.RS_DESC
    FROM SYSADM.REASONSTATUS_ALL A
   WHERE A.RS_ID = CH_REASON) REASON_DES,
 '0' FLAG_PROCESADO,
 TIM.TFUN105_TIPO_PRODUCTO(dt.dn_num) TIPO_PRODUCTO
  FROM SYSADM.contract_all     co,
       SYSADM.customer_all     cu,
       SYSADM.ccontact_all     cc,
       SYSADM.rateplan         rp,
       SYSADM.info_contr_combo icc,
       tim.pp_datos_contrato   dt,
       (select chi.co_id,max(chi.ch_validfrom) ch_validfrom SYSADM.contract_history  chi where chi.ch_seqno = 2 group by chi.co_id )ch
 WHERE cu.prgcode in (2)   
   AND ch.co_id = co.co_id
   AND co.customer_id = cu.customer_id
   AND co.customer_id = cc.customer_id
   AND co.tmcode = rp.tmcode
   AND co.co_id = icc.co_id
   AND cc.ccbill = 'X'
   AND co.customer_id = dt.customer_id
   AND co.co_id = dt.co_id
--   AND (ch.ch_validfrom BETWEEN TO_DATE('20101115', 'YYYYMMDDhh24miss') AND TO_DATE('20101118', 'YYYYMMDDhh24miss')) 
--   AND ch.ch_validfrom > TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha desde
   AND ch.ch_validfrom < TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha hasta
   AND  dt.dn_num in (997351247)

Open in new window


I think it will do what you re asking for.
0
 
enrique_aeoAuthor Commented:
O have this error
Invalid identifier: CH.CH_REASON reason_id,

SELECT /*+DRIVING_SITE(ch)*/ /*+DRIVING_SITE(co)*/ /*+DRIVING_SITE(cu)*/ /*+DRIVING_SITE(cc)*/ /*+DRIVING_SITE(rp)*/ /*+DRIVING_SITE(icc)*/ /*+DRIVING_SITE(dt)*/
 dt.dn_num NRO_TELEFONO,
 dt.co_id,
 ch.ch_validfrom FECHA_ACUERDO,
 dt.customer_id,
 cu.custcode,
 cu.cscompregno RUC_DNI,
 cc.ccname RAZON_SOCIAL,
 cc.ccaddr1 || ' ' || cc.ccaddr3 || ' ' || cc.ccstreet || ' ' || cc.cccity DIRECCION,
 cu.prgcode TIPO_CLIENTE,
 dt.tmcode CODIGO_PLAN,
 decode(substr(rp.shdes, 0, 3), 'CON', 'C', 'P') TIPO_PLAN,
 'A' ESTADO_LINEA,
 --F_OBTENER_CF(dt.tmcode) CARGO_FIJO,
 --F_OBTENER_DEVIATING(dt.co_id, dt.tmcode) DEVIATING,
 decode(cu.cscusttype,
        'C',
        nvl(icc.combo14, icc.combo06),
        'B',
        nvl(icc.combo06, icc.combo14),
        'LIBRE') ACUERDO,
 CH.CH_REASON reason_id,
 (SELECT A.RS_DESC
    FROM SYSADM.REASONSTATUS_ALL A
   WHERE A.RS_ID = CH_REASON) REASON_DES,
 '0' FLAG_PROCESADO,
 TIM.TFUN105_TIPO_PRODUCTO(dt.dn_num) TIPO_PRODUCTO
  FROM SYSADM.contract_all     co,
       SYSADM.customer_all     cu,
       SYSADM.ccontact_all     cc,
       SYSADM.rateplan         rp,
       SYSADM.info_contr_combo icc,
       tim.pp_datos_contrato   dt,
       (select chi.co_id,max(chi.ch_validfrom) ch_validfrom SYSADM.contract_history  chi where chi.ch_seqno = 2 group by chi.co_id )ch
 WHERE cu.prgcode in (2)  
   AND ch.co_id = co.co_id
   AND co.customer_id = cu.customer_id
   AND co.customer_id = cc.customer_id
   AND co.tmcode = rp.tmcode
   AND co.co_id = icc.co_id
   AND cc.ccbill = 'X'
   AND co.customer_id = dt.customer_id
   AND co.co_id = dt.co_id
--   AND (ch.ch_validfrom BETWEEN TO_DATE('20101115', 'YYYYMMDDhh24miss') AND TO_DATE('20101118', 'YYYYMMDDhh24miss'))
--   AND ch.ch_validfrom > TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha desde
   AND ch.ch_validfrom < TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha hasta
   AND  dt.dn_num in (997351247)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Connect With a Mentor Commented:
You have a subquery aliased as ch, but the subquery does not select ch_reason.
0
 
enrique_aeoAuthor Commented:
I have the same results

SELECT /*+DRIVING_SITE(ch)*/ /*+DRIVING_SITE(co)*/ /*+DRIVING_SITE(cu)*/ /*+DRIVING_SITE(cc)*/ /*+DRIVING_SITE(rp)*/ /*+DRIVING_SITE(icc)*/ /*+DRIVING_SITE(dt)*/
 dt.dn_num NRO_TELEFONO,
 dt.co_id,
 ch.ch_validfrom FECHA_ACUERDO,
 dt.customer_id,
 cu.custcode,
 cu.cscompregno RUC_DNI,
 cc.ccname RAZON_SOCIAL,
 cc.ccaddr1 || ' ' || cc.ccaddr3 || ' ' || cc.ccstreet || ' ' || cc.cccity DIRECCION,
 cu.prgcode TIPO_CLIENTE,
 dt.tmcode CODIGO_PLAN,
 decode(substr(rp.shdes, 0, 3), 'CON', 'C', 'P') TIPO_PLAN,
 'A' ESTADO_LINEA,
 --F_OBTENER_CF(dt.tmcode) CARGO_FIJO,
 --F_OBTENER_DEVIATING(dt.co_id, dt.tmcode) DEVIATING,
 decode(cu.cscusttype,
        'C',
        nvl(icc.combo14, icc.combo06),
        'B',
        nvl(icc.combo06, icc.combo14),
        'LIBRE') ACUERDO,
 CH.CH_REASON reason_id,
 (SELECT A.RS_DESC
    FROM SYSADM.REASONSTATUS_ALL A
   WHERE A.RS_ID = CH_REASON) REASON_DES,
 '0' FLAG_PROCESADO,
 TIM.TFUN105_TIPO_PRODUCTO(dt.dn_num) TIPO_PRODUCTO
  FROM SYSADM.contract_all     co,
       SYSADM.customer_all     cu,
       SYSADM.ccontact_all     cc,
       SYSADM.rateplan         rp,
       SYSADM.info_contr_combo icc,
       tim.pp_datos_contrato   dt,
       (select CH_REASON,chi.co_id,max(chi.ch_validfrom) ch_validfrom
        from SYSADM.contract_history  chi
        where chi.ch_seqno = 2 group by CH_REASON,chi.co_id ) ch
 WHERE cu.prgcode in (2)  
   AND ch.co_id = co.co_id
   AND co.customer_id = cu.customer_id
   AND co.customer_id = cc.customer_id
   AND co.tmcode = rp.tmcode
   AND co.co_id = icc.co_id
   AND cc.ccbill = 'X'
   AND co.customer_id = dt.customer_id
   AND co.co_id = dt.co_id
--   AND (ch.ch_validfrom BETWEEN TO_DATE('20101115', 'YYYYMMDDhh24miss') AND TO_DATE('20101118', 'YYYYMMDDhh24miss'))
--   AND ch.ch_validfrom > TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha desde
   AND ch.ch_validfrom < TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha hasta
   AND  dt.dn_num in (997351247)
0
 
enrique_aeoAuthor Commented:
query which returns 2 or more results, can help me modify it to return the largest fecha_acuerdo
0
 
awking00Commented:
...
(select CH_REASON,chi.co_id, ch_validfrom,
       ,row_number() over (partition by chi.co_id order by ch_validfrom desc) rn
        from SYSADM.contract_history  chi
        where chi.ch_seqno = 2) ch
where ...
and ch.rn = 1
0
 
enrique_aeoAuthor Commented:
and modify the query but I have the same results
 
SELECT
 dt.dn_num NRO_TELEFONO,
 dt.co_id,
 ch.ch_validfrom FECHA_ACUERDO,
 TIM.TFUN105_TIPO_PRODUCTO(dt.dn_num) TIPO_PRODUCTO
  FROM SYSADM.contract_all     co,
       SYSADM.customer_all     cu,
       SYSADM.ccontact_all     cc,
       SYSADM.rateplan         rp,
       SYSADM.info_contr_combo icc,
       tim.pp_datos_contrato   dt,
        (select CH_REASON,chi.co_id,ch_validfrom,
                ROW_NUMBER() OVER (PARTITION BY chi.co_id ORDER BY ch_validfrom DESC) RN
        from SYSADM.contract_history  chi
        where chi.ch_seqno = 2) ch
 WHERE cu.prgcode in (2)  
   AND ch.co_id = co.co_id
   AND co.customer_id = cu.customer_id
   AND co.customer_id = cc.customer_id
   AND co.tmcode = rp.tmcode
   AND co.co_id = icc.co_id
   AND cc.ccbill = 'X'
   AND co.customer_id = dt.customer_id
   AND co.co_id = dt.co_id
   AND CH.RN = 1
   AND ch.ch_validfrom < TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha hasta
   AND  dt.dn_num in (997351247)
   and ch.rn = 1
0
 
enrique_aeoAuthor Commented:
Please experts, I made the changes suggested and still does not work, please appreciate their support
0
 
NikolasGConnect With a Mentor Commented:
Can you give as a sample of the returned values?
From what I see in the select it shouldn't give you back duplicate values due to the contract date. (the one in the sub query).
Maybe you have joins returning more than one values in the other tables.
 
can you try running the following and check if ti gives you also more than one rows?
SELECT /*+DRIVING_SITE(ch)*/ /*+DRIVING_SITE(co)*/ /*+DRIVING_SITE(cu)*/ /*+DRIVING_SITE(cc)*/ /*+DRIVING_SITE(rp)*/ /*+DRIVING_SITE(icc)*/ /*+DRIVING_SITE(dt)*/
 dt.dn_num NRO_TELEFONO,
 dt.co_id,
 ch.ch_validfrom FECHA_ACUERDO,
 dt.customer_id,
 cu.custcode,
 cu.cscompregno RUC_DNI,
 cc.ccname RAZON_SOCIAL,
 cc.ccaddr1 || ' ' || cc.ccaddr3 || ' ' || cc.ccstreet || ' ' || cc.cccity DIRECCION,
 cu.prgcode TIPO_CLIENTE,
 dt.tmcode CODIGO_PLAN,
 decode(substr(rp.shdes, 0, 3), 'CON', 'C', 'P') TIPO_PLAN,
 'A' ESTADO_LINEA,
 --F_OBTENER_CF(dt.tmcode) CARGO_FIJO,
 --F_OBTENER_DEVIATING(dt.co_id, dt.tmcode) DEVIATING,
 decode(cu.cscusttype,
        'C',
        nvl(icc.combo14, icc.combo06),
        'B',
        nvl(icc.combo06, icc.combo14),
        'LIBRE') ACUERDO,
 '0' FLAG_PROCESADO,
 TIM.TFUN105_TIPO_PRODUCTO(dt.dn_num) TIPO_PRODUCTO
  FROM SYSADM.contract_all     co,
       SYSADM.customer_all     cu,
       SYSADM.ccontact_all     cc,
       SYSADM.rateplan         rp,
       SYSADM.info_contr_combo icc,
       tim.pp_datos_contrato   dt,
       (select chi.co_id,max(chi.ch_validfrom) ch_validfrom SYSADM.contract_history  chi where chi.ch_seqno = 2 group by chi.co_id )ch
 WHERE cu.prgcode in (2)   
   AND ch.co_id = co.co_id
   AND co.customer_id = cu.customer_id
   AND co.customer_id = cc.customer_id
   AND co.tmcode = rp.tmcode
   AND co.co_id = icc.co_id
   AND cc.ccbill = 'X'
   AND co.customer_id = dt.customer_id
   AND co.co_id = dt.co_id
--   AND (ch.ch_validfrom BETWEEN TO_DATE('20101115', 'YYYYMMDDhh24miss') AND TO_DATE('20101118', 'YYYYMMDDhh24miss')) 
--   AND ch.ch_validfrom > TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha desde
   AND ch.ch_validfrom < TO_DATE('20101115', 'YYYYMMDDhh24miss') --fecha hasta
   AND  dt.dn_num in (997351247)                                           

Open in new window

0
 
awking00Connect With a Mentor Commented:
Does the attribute CH_REASON reside in the SYSADM.contract_history table? If so, what does the following query produce?
select * from
(select CH_REASON,chi.co_id,ch_validfrom,
                ROW_NUMBER() OVER (PARTITION BY chi.co_id ORDER BY ch_validfrom DESC) RN
        from SYSADM.contract_history  chi
        where chi.ch_seqno = 2)
where rn = 1;
0
 
enrique_aeoAuthor Commented:
I execute the query but i have This error
Ora - 01114
0
 
NikolasGConnect With a Mentor Commented:
That is very strange. Did that error appear when you run other queries?
Do you have enough free space in the disk where the database is?
which query did you try to execute?
0
 
enrique_aeoAuthor Commented:
This:
select * from
(select CH_REASON,chi.co_id,ch_validfrom,
                ROW_NUMBER() OVER (PARTITION BY chi.co_id ORDER BY ch_validfrom DESC) RN
        from SYSADM.contract_history  chi
        where chi.ch_seqno = 2)
where rn = 1;
0
 
enrique_aeoAuthor Commented:
Hi Experts
This query

 a mi yahoo por favor: enrique_aeo@yahoo.com
 
select V_DN_NUM,
       N_CO_ID,
       D_CH_VALIDFROM,
       N_CUSTOMER_ID,
       V_CUSTCODE,
       V_CSCOMPREGNO,
       V_CCNAME,
       V_DIRECCION,
       V_PRGCODE,
       N_TMCODE,
       V_TIPO_PLAN,
       V_ACUERDO,
       N_CH_REASON,
       V_REASON_DES,
       D_FECHA_CARGA,
       V_DESC_TIPO_OPERACION
  from sigat_activaciones_tmp act
 where act.v_dn_num in 997351247

I need This Max valué     D_CH_VALIDFROM
0
 
enrique_aeoAuthor Commented:
Thank you all for your help. I resolved the problem in 2 parts. First find the maximum date and store it in a variable. Then use that variable in my query
0
All Courses

From novice to tech pro — start learning today.