Solved

query which returns 2 or more results

Posted on 2012-03-15
16
413 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:enrique_aeo
  • 9
  • 4
  • 3
16 Comments
 
LVL 6

Assisted Solution

by:NikolasG
NikolasG earned 250 total points
ID: 37724526
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
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
ID: 37724625
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
 

Author Comment

by:enrique_aeo
ID: 37737960
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 37738193
You have a subquery aliased as ch, but the subquery does not select ch_reason.
0
 

Author Comment

by:enrique_aeo
ID: 37738426
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
 

Author Comment

by:enrique_aeo
ID: 37738447
query which returns 2 or more results, can help me modify it to return the largest fecha_acuerdo
0
 
LVL 31

Expert Comment

by:awking00
ID: 37738506
...
(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
 

Author Comment

by:enrique_aeo
ID: 37739946
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:enrique_aeo
ID: 37745603
Please experts, I made the changes suggested and still does not work, please appreciate their support
0
 
LVL 6

Assisted Solution

by:NikolasG
NikolasG earned 250 total points
ID: 37745993
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 37747243
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
 

Author Comment

by:enrique_aeo
ID: 37747817
I execute the query but i have This error
Ora - 01114
0
 
LVL 6

Assisted Solution

by:NikolasG
NikolasG earned 250 total points
ID: 37747836
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
 

Author Comment

by:enrique_aeo
ID: 37747942
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
 

Author Comment

by:enrique_aeo
ID: 37754494
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
 

Author Closing Comment

by:enrique_aeo
ID: 37760041
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now