Si aprendes como obtener en un SELECT el artículo más comprado podrás desarrollar una técnica que te permitirá resolver problemas similares en otros contextos en tus PL-SQL Packages en Oracle.
Obtener en un SELECT el artículo mas comprado de un proveedor en tus PL-SQL Packages en Oracle, puede parecer una tarea muy difícil de realizar, pero en realidad este problema se puede solucionar de varias formas.
A continuación en el video te muestro una de las técnicas para solucionar este problema en tus PL-SQL Packages en Oracle, que me gusta mucho porque es bastante eficiente y muy fácil de implementar.
Mira el Video (clic en el centro de la imagen)
TÚ REPORTE: “Cómo Construir un SELECT Eficiente en ORACLE en Solo 7 Pasos”
Esta misma técnica se puede implementar para buscar el Artículo menos comprado, el más Vendido o el menos Vendido en tus PL-SQL Packages en Oracle.
Te propongo que pruebes de inmediato este ejemplo en tu Base de Datos con tus propias tablas, PL-SQL Procedures en Oracle y PL-SQL Function en Oracle, para que comiences a ejercitar la mano y aprendas a dominar esta nueva técnica en tus PL-SQL Packages en Oracle.
Estoy muy interesado en conocer tu opinión y/o comentario sobre este artículo, también cuéntame sobre qué es lo que quieres saber de PL-SQL Packages en Oracle y/o dime cuál es tu principal frustración a la hora de trabajar con PL-SQL Packages en Oracle. Estaré muy atento respondiendo tus preguntas y/o comentarios, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Director del Curso “Domina Tus Packages En Oracle“
Me gustaria compartir tus experiencias. Gracias
Hola Carlos, gracias por comunicarte y excelente tu comentario.
PD: Ya obtuviste mi reporte gratuito de ‘Cómo Construir un Select Eficiente en Oracle en Solo 7 Pasos’, si aún no lo has hecho puedes descargarlo desde aquí:
https://DominaTusPackagesEn0racle.com
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Muy buenos tus articulos. Felicitaciones y gracias.
Carlos Andiazabal
Milan – Italy
Hola Carlos, exelente tu comentario, te estare avisando de cada material nuevo que este sacando, gracias por comunicarte.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
https://dominatuspackagesen0racle.com/detalles/
Hola, muy interesantes y buenos tus articulos. Felicitaciones y gracias.
Popayán – Colombia
Hola Luis, gracias por comunicarte y excelente tu comentario, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
SQL can execute qureeis against a database, retrieve,update,insert data from a database and empty refer to an empty result of no rows and NULL refer to a table with one row holding a NULL value.
Hola Eduardo, gracias por comunicarte y muy bueno tu comentario, solo quiero aclarar que una tabla vacia no tiene ninguna fila y el NULL se refiere a un campo que esta vacio, es decir, que no tiene ningún dato, gracias.
PD: Ya obtuviste mi reporte gratuito de ‘Cómo Construir un Select Eficiente en Oracle en Solo 7 Pasos’, si aún no lo has hecho puedes descargarlo desde aquí:
https://DominaTusPackagesEn0racle.com
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
sintaxis y ejemplo de como Acceder a 2 bases de datos desde Store procedure
Hola Voctory, gracias por comunicarte y muy buena tu pregunta, para acceder a dos Bases de Datos primero debes tener creado un DbLink a la base de datos que quieres accesar, esto se lo debes solicitar a tu DBA, la sintaxis es la siguiente:
CREATE PUBLIC DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING ;
Ahora para consultar una tabla de la base de datos de venta, se hace asi:
select * from t_tit_ventas@bd_ventas;
PD: Ya reservaste tu cupo para mi próximo Seminario GRATIS sobre “Como Construir un SELECT de Múltiples Tablas” – Imperdible, si aún no te has inscrito puedes hacerlo desde aquí:
https://dominatuspackagesen0racle.com/v/seminario-smt/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Comparto una forma de obtener el articulo mas comprado, entre todos los proveedores, con un solo anidamiento.
SELECT may.*
FROM (SELECT cdet.id_proveedor, cdet.id_articulo, SUM(cdet.cant_compra) cant_comprada
FROM t_comras_det cdet
GROUP BY cdet.id_proveedor, cdet.id_articulo
ORDER BY SUM(cdet.cant_comprada) DESC) may
WHERE ROWNUM = 1
Hola Ivan, gracias por comunicarte y excelente tu aporte, agregar un SUM dentro del ORDER BY evita realizar un segundo anidamiento y se obtiene el mismo resultado, de todos modos se debe tener presente que cuando se trabaja con miles de registros y se hace un order by y además un sum puede afectar la velocidad de la consulta, pero todo depende de la cantidad de registros y del modelo de datos.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Saludos amigo!
Tengo una situación similar, Auditoria me esta solicitando un reporte de ventas por bodega y por articulo en donde me indique cual fue la última venta (mas reciente) y el ultimo precio con el que se vendio cada articulo. Ya logré obtener la última venta, pero al colocarle el ultimo precio se me repiten los articulos tantas veces como se haya vendido…sinceramente ya no se que hacer. Agradecería mucho tu ayuda.
Estoy comenzando con Oracle y ya tengo el libro sobre “Como construir un Select eficiente” me ayudo mucho.
Saludos
Hola Jonathan, gracias por comunicarte, si ya obtuviste la última venta el último precio esta en el mismo registro y es mas fácil obtenerlo, no se como estas haciendo la query, pero te recomiendo que incluyas el precio dentro del group by, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Gracias Roberto, te pongo un ejemplo de lo que estoy haciendo:
select a.codigo_bodega,max(a.fecha) FECHA,B.NO_ARTI,B.PRECIO
from arfafe a,ARFAFL B –ENCABEZADO Y DETALLE DE LA FACTURA
WHERE A.NO_FACTU=B.NO_FACTU–JOIN CON EL NUMERO DE TRANSACCION ENTRE ENCABEZADO Y DETALLE
AND trunc(a.fecha) between to_date(’01-01-2012′,’dd-mm-yyyy’) and to_date(’31-12-2012′,’dd-mm-yyyy’)
and A.tipo_doc not in(‘AN’,’NV’)–NO TOMA ANULACIONES NI DEVOLUCIONES
AND NVL(A.IND_ANU_DEV,’N’) NOT IN (‘A’,’D’)–BUSCA QUE NO TENGA EL INDICADOR DE DEVOLUCION EN EL ENCABEZADO
AND B.NO_ARTI=’06971′
group by a.codigo_bodega,B.NO_ARTI,B.PRECIO
order by 3,1
esto me devuelve lo siguiente:
CODIGO_BODEGA FECHA NO_ARTI PRECIO
02 12/06/2012 00:00:00 06971 1.29
02 10/16/2012 00:00:00 06971 1.34
03 10/19/2012 00:00:00 06971 1.79
07 11/14/2012 00:00:00 06971 1.25
07 10/04/2012 00:00:00 06971 1.34
07 09/24/2012 00:00:00 06971 1.56
07 10/30/2012 00:00:00 06971 2.01
Como podras notar, el articulo 06971 me aparece 2 veces en la bodega 02 (toma todas las ventas que se dieron para ese articulo) pero lo que yo busco es que el query solo me devuelva el registro con fecha mas reciente de cada bodega.
Espero haber dado una idea de mi problema, gracias por tu ayuda de antemano!
Saludos,
Jonathan.
Hola Jonathan, una de las formas en que puedes resolver este problema es haciendo uso de las subquerys, obteniendo en la primer subquery la ultima venta y en la segunda subquery cada venta con su precio, finalmente se unen los resultados filtrando por la fecha de la ultima venta y así obtener el precio que corresponde, puedes hacer algo como lo siguiente:
select
det.codigo_bodega, det.fecha, det.NO_ARTI, det.PRECIO
from (
select a.codigo_bodega,max(a.fecha) FECHA,B.NO_ARTI
from arfafe a,ARFAFL B –ENCABEZADO Y DETALLE DE LA FACTURA
WHERE A.NO_FACTU=B.NO_FACTU–JOIN CON EL NUMERO DE TRANSACCION ENTRE ENCABEZADO Y DETALLE
AND trunc(a.fecha) between to_date(’01-01-2012′,’dd-mm-yyyy’) and to_date(’31-12-2012′,’dd-mm-yyyy’)
and A.tipo_doc not in(‘AN’,’NV’)–NO TOMA ANULACIONES NI DEVOLUCIONES
AND NVL(A.IND_ANU_DEV,’N’) NOT IN (‘A’,’D’)–BUSCA QUE NO TENGA EL INDICADOR DE DEVOLUCION EN EL ENCABEZADO
AND B.NO_ARTI=’06971′
group by a.codigo_bodega,B.NO_ARTI
) may, — En esta subquery se obtiene la última venta.
(select a.codigo_bodega,max(a.fecha) FECHA,B.NO_ARTI,B.PRECIO
from arfafe a,ARFAFL B –ENCABEZADO Y DETALLE DE LA FACTURA
WHERE A.NO_FACTU=B.NO_FACTU–JOIN CON EL NUMERO DE TRANSACCION ENTRE ENCABEZADO Y DETALLE
AND trunc(a.fecha) between to_date(’01-01-2012′,’dd-mm-yyyy’) and to_date(’31-12-2012′,’dd-mm-yyyy’)
and A.tipo_doc not in(‘AN’,’NV’)–NO TOMA ANULACIONES NI DEVOLUCIONES
AND NVL(A.IND_ANU_DEV,’N’) NOT IN (‘A’,’D’)–BUSCA QUE NO TENGA EL INDICADOR DE DEVOLUCION EN EL ENCABEZADO
AND B.NO_ARTI=’06971′
group by a.codigo_bodega,B.NO_ARTI,B.PRECIO
) det — En esta subquery se obtienen los precios por cada venta.
where may.codigo_bodega = det.codigo_bodega
and may.fecha = det.fecha — Aquí se filtra por la fecha de la última venta.
and may.NO_ARTI = det.NO_ARTI
Como tu conoces el modelo trata de incluir campos ID para unir las subquerys para mejorar el tiempo de respuesta, espero se sirva.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
En efecto Roberto, así funciono perfectamente. Te agradezco mucho tu ayuda, no hay duda que tienes mucha experiencia en esto y lo mejor es que la compartes.
Saludos
Jonathan
Muy interesante el artículo para saber cual es el material que más ha entregado un proveedor, pero me gustaría saber que se debe cambiar si se quiere saber mediante sql, a que proveedor corresponde, que lo haga el programa, no que el programador digite el codigo del proveedor.
Gracias.
Hola Henry, gracias por comunicarte y por tu comentario, para obtener a que proveedor corresponde el articulo mas comprado lo único que debes hacer es sacar la linea ‘where id_proveedor=1002’ y listo, de esta forma no necesitas digitar el proveedor, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
https://dominatuspackagesen0racle.com/
Excelentes artículos, más adelante que este más solvente ($$), me interesaría el curso.
Muchas gracias pos su respuesta.
Hola Henry, gracias por comunicarte, me da gusto haber podido ayudarte y resolver tus dudas, ahora si deseas especializarte en el lenguaje PL-SQL tengo un cupo reservado para ti en mi curso “Domina Tus Packages en Oracle”, puedes acceder desde el siguiente enlace:
https://dominatuspackagesen0racle.com/detalles/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Estimado Buenas Tardes si me ayudan con la relizacion de paquete realizacion de selects cn pl/sql
por su ayuda anticipo mis agradecimientos
Hola Ramiro, gracias por comunicarte y por tu comentario, claro que podemos prestarte ayuda en cuanto a resolver las dudas que tengas al trabajar con pl-sql, en este blog puedes encontrar bastante información sobre este tema, gracias.
PD: En cuanto a realización de Selects, ya obtuviste mi libro-reporte gratuito sobre ‘Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aún no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
my buenas tardes si me podria ayudar con este ejemplo
crear tablas dentro del package en oracle no ce como hacer
Hola Blanca, gracias por comunicarte, para crear una tabla dentro de un package usa la instrucción execute immediate de la siguiente forma:
excecute immediate(‘create table tu_tabla(
campo1 number,
campo2 date,
campo3 varchar2(10) )’);
PD: Ya obtuviste mi libro-reporte “Cómo Construir un SELECT Eficiente en Oracle en Solo 7 Pasos”, si aún no lo tienes puedes obtenerlo desde aquí: https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
hola buen dia
estoy haciendo un reporte pero su ejecucion es un poco lenta me puedes ayudar a optimizar mi query, el query funciona perfectamente pero es un poco lento espero y me puedan ayudar
————————————–
SELECT despido,ingreso,
FICHA,END_DATE,START_DATE,
SUM(TRES_MESES) TRES_MESES,
sum(DIAS_20) DIAS_20,
sum(PRIMA_ANT) PRIMA_ANT
,sum(GRATIFICACION) GRATIFICACION,
sum(PERCEPCIONES) PERCEPCIONES,
sum(ISPT) ISPT,
sum(Embargo) Embargo,
sum(DESCUENTO) DESCUENTO ,
sum(Deducciones) Deducciones,
sum(PERCEPCIONES- Deducciones) Neto,
SUBSTR((select to_char(sysdate,’DD/MM/YYYY HH24:MM’) from dual),1,17) FECHA,
(select TO_CHAR(TO_DATE(PAY_ADVICE_DATE),’DD/MM/YYYY’) from PER_TIME_PERIODS WHERE PAYROLL_ID= &TIPO_NOMINA AND time_period_id=&PERIODO_ID) FECHA_PAGO
,NOMINA
,NOMBRE
,RFC,NIVEL,
TO_CHAR(NVL(DIAS_LAB,0))DIAS_LAB ,
despido,ingreso,
ausencias,
LIQ,
SUM(SAL_MIN) SAL_MIN,ASSIGNMENT_ID
FROM
(
SELECT
DISTINCT(FICHA), END_DATE,START_DATE,
TRES_MESES,
DIAS_20,
PRIMA_ANT,
(TRES_MESES+ DIAS_20+PRIMA_ANT+ GRATIFICACION)PERCEPCIONES,
ISPT,
(e1+e2+e3) Embargo,
(DESCUENTO +ISPT + (e1+e2+e3)) Deducciones
,GRATIFICACION
,DESCUENTO
,NOMINA
,NOMBRE
,RFC,0 NIVEL,
NVL((((to_date(substr(DESPIDO,1,10),’DD/MM/YYYY’) – to_date(substr(INGRESO,1,10),’DD/MM/YYYY’) ))- ausencias),0) Dias_LAB,
ausencias,despido,ingreso,
LIQ, 0 SAL_MIN, ASSIGNMENT_ID
FROM
(
SELECT
PPF.attribute1 FICHA, PT.END_DATE END_DATE, PT.START_DATE START_DATE,
ptp.period_name,PPF.ORIGINAL_DATE_OF_HIRE ingreso, PPS.actual_termination_date despido,
(to_date(substr(PPS.actual_termination_date,1,10),’DD/MM/YYYY’) – to_date(substr(PPF.ORIGINAL_DATE_OF_HIRE,1,10),’DD/MM/YYYY’) ) TOTAL_DIAS_LAB,
TRANSLATE(PPF.FULL_NAME,
‘áéíóúàèìòùãõâêîôôäëïöüçñÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇÑ’,
‘aeiouaeiouaoaeiooaeioucnAEIOUAEIOUAOAEIOOAEIOUCN’) NOMBRE ,
PPF.NATIONAL_IDENTIFIER RFC,
ASF.ASSIGNMENT_ID,
ASF.JOB_ID,
NVL(CUSPAY.GPR_PAY_GET_ABSENCE_DAYS(&TIPO_NOMINA,VPAS.PERSON_ID,&PERIODO_ID, PPF.ORIGINAL_DATE_OF_HIRE),0) ausencias,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’3MESES”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0)TRES_MESES,
–NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’3MESESP”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) LIQ,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’PORCENTAJE”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) LIQ,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’20DIAS”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) DIAS_20,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’PRIMA_ANT”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) PRIMA_ANT,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’ISR-IPC”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) ISPT,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’EMB-1”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) E1,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’EMB-2”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) E2,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’EMB-3”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) E3,
NVL(cuspay.GPR_PAY_GET_ELEM_FINIQUITO(”’GRAT_FIN”’, &TIPO_NOMINA,ASF.ASSIGNMENT_ID, &PERIODO_ID,”’Q”’),0) GRATIFICACION,
0 DESCUENTO
,PPFF.payroll_name NOMINA
,SUBSTR(VPAS.JOB_NAME,1,4 ) NIVEL
,NVL(VPAS.PROPOSED_SALARY_N,0) SAL_MIN
FROM
per_people_f PPF
,per_assignments_f ASF
,pay_assignment_actions PAA,
PAY_PAYROLL_ACTIONS PPA,
pay_payrolls_f PPFF
,PER_TIME_PERIODS PTP
,(
select START_DATE, END_DATE, PAYROLL_ID
from PER_TIME_PERIODS
WHERE PAYROLL_ID=&TIPO_NOMINA
AND time_period_id=&PERIODO_ID
) pt
,GPR_PAY_ASSIGNMENTS VPAS
,per_periods_of_service PPS
,pay_consolidation_sets PCS
————–
where
PPF.effective_end_date between pt.START_DATE and pt.END_DATE
and ASF.effective_end_date between pt.START_DATE and pt.END_DATE
—
and
PPF.person_id = ASF.person_id
and PAA.assignment_id = ASF.assignment_id
and PAA.payroll_action_id = PPA.payroll_action_id
and PPA.payroll_id =&TIPO_NOMINA
and PPFF.payroll_id = &TIPO_NOMINA
—
AND ASF.PAYROLL_ID= &TIPO_NOMINA
AND ASF.payroll_id = PT.PAYROLL_ID
—–
AND (PTP.time_period_id =&PERIODO_ID)
AND PPF.PERSON_ID = ASF.PERSON_ID
AND VPAS.time_period_id = &PERIODO_ID
AND VPAS.ASSIGNMENT_ID=ASF.assignment_id
—
AND ASF.person_id = PPS.person_id
and (PPS.actual_termination_date between PT.start_DATE and PT.end_DATE) — FECHA FINAL DEL PERIODO Y FECHA INICIAL
— and PT.end_DATE between
–and ASF.ASSIGNMENT_ID=10734 — melo
— and ASF.ASSIGNMENT_ID=10455 –sc
AND PPA.CONSOLIDATION_SET_ID =PCS.CONSOLIDATION_SET_ID
AND PCS.CONSOLIDATION_SET_NAME=’FINIQUITO’
) AB
— WHERE ELEMENTO IN(9652,9612,9781)
ORDER BY NOMBRE
) ABB
–WHERE (PERCEPCIONES- Deducciones) > 0
GROUP BY
FICHA,
NOMINA
,NOMBRE
,RFC,NIVEL,
ASSIGNMENT_ID,DIAS_LAB,LIQ, ausencias, despido,ingreso,END_DATE,START_DATE
ORDER BY NOMBRE
Hola Julio, gracias por comunicarte, si tu query funciona pero es lenta puedes optimizarla verificando lo siguiente:
– Todos los campos llave que usas para unir las tablas deben tener un índice creado.
– No es recomendable usar sub-select en las columnas a menos que la tabla PER_TIME_PERIODS sea pequeña con acceso rápido.
– Revisa que la función GPR_PAY_GET_ELEM_FINIQUITO sea rápida ya que la ocupas en muchas columnas.
– Los order by son lentos y estas haciendo 2 por el nombre.
– Revisa muy bien si es necesario realmente hacer 3 sub-querys aveces puedes obtener toda la información solo en 1 query.
– Elimina todos los access full que tengas a tus tablas.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Excelente página que me ha permitido mejorar mucho la programación. Se agradece esta forma de apoyar.
Hola Alex, gracias por comunicarte y por tu comentario, me da mucho gusto ver que la información recibida te sea de utilidad, si lo que has visto en el blog te ha gustado te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les interese este contenido, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.