enrique_aeo
asked on
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
image001.png
query.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
--F_OBTENER_DEVIATING(dt.
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
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(ch
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)
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)
--F_OBTENER_DEVIATING(dt.
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
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(ch
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)
ASKER
query which returns 2 or more results, can help me modify it to return the largest fecha_acuerdo
...
(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
(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
ASKER
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
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_val
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
SELECT
dt.dn_num NRO_TELEFONO,
dt.co_id,
ch.ch_validfrom FECHA_ACUERDO,
TIM.TFUN105_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_val
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
ASKER
Please experts, I made the changes suggested and still does not work, please appreciate their support
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I execute the query but i have This error
Ora - 01114
Ora - 01114
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This:
select * from
(select CH_REASON,chi.co_id,ch_val
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;
select * from
(select CH_REASON,chi.co_id,ch_val
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;
ASKER
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
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
ASKER
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
ASKER
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)
--F_OBTENER_DEVIATING(dt.
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
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_valid
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)