Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

query which returns 2 or more results

Posted on 2012-03-15
16
Medium Priority
?
441 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 1000 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 32

Accepted Solution

by:
awking00 earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 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 32

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
 

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 1000 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 32

Assisted Solution

by:awking00
awking00 earned 1000 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 1000 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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

877 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