Solved

query which returns 2 or more results

Posted on 2012-03-15
16
419 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 32

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 30
Compare a numeric value to a varchar w/o getting an error 4 21
SQL Group By Question 4 21
Update one rows based on previous row 5 9
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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