How to configure sql query to use dateranger

Queries and Resources
1

i want to extract information by date range by means of a sql query, this is the query,

what could be my error (I am new to this)
---> SERIES DESPACHADAS POR RANGO FECHA

SELECT SYSDATE FCH_CONSULTA,
rcpn.bdin_cdg BODEGA,
srar.artl_cdg SKU,
(
SELECT LISTAGG(prgn_obs, ', ') WITHIN GROUP (ORDER BY prgn_obs) "FAMILIA"
FROM prgn
WHERE prgn_id_tabla = 'MSXF_' || '966893106' AND prgn_valor = srar.artl_cdg
) FAMILIA,
srar.srar_cdg SERIE,
DECODE(srar.odpd_nmr_id, NULL, 'En Bodega', 'Despachada') Estado,
DECODE(srar.odpd_nmr_id, NULL,
COALESCE(TO_CHAR(rcpn.rcpn_fch_cierre_recepcion, 'Mon dd, yyyy'), TO_CHAR(rcpn.rcpn_fch_ing, 'Mon dd, yyyy')),
TO_CHAR(odpd.odpd_fch_real_despacho, 'Mon dd, yyyy')) Fecha_UM, --ultimo MOV
TO_CHAR(rcpn.rcpn_fch_ing, 'Mon dd, yyyy') Fecha_ING,
TO_CHAR(rcpn.rcpn_fch_cierre_recepcion, 'Mon dd, yyyy') fch_cierre_rcpn,
TO_CHAR(odpd.odpd_fch_real_despacho, 'Mon dd, yyyy') fch_real_despacho_Mcnzdo,
DTPD.DTPD_CNT_DESPACHADA CANTIDAD_DESPACHADA,
DECODE(odpd.TPOP_CDG, 'TF', 'TRANSFERENCIA', 'DE', 'DESPACHO', odpd.TPOP_CDG) TIPO_OPERACION,
MODOPER_TRBK.PRGN_OBS TIPO_PEDIDO,
odpd.odpd_nmr_folio folio_OP,
rcpn.tpee_cdg RCPN_ESTADO,
odpd.tpee_cdg OP_ESTADO,
odpd.odpd_orden_servicio OS,
slpd.slpd_numero,
slpd.*
FROM srar
INNER JOIN rcpn ON srar.rcpn_cdg = rcpn.rcpn_cdg
INNER JOIN odpd ON srar.odpd_nmr_id = odpd.odpd_nmr_id
INNER JOIN dtpd ON srar.odpd_nmr_id = dtpd.odpd_nmr_id
AND dtpd.dtpd_eliminada = 0
AND srar.artl_cdg = dtpd.artl_cdg AND srar.dtpd_linea = dtpd.dtpd_linea
LEFT JOIN slpd ON odpd.odpd_nmr_folio = TO_CHAR(slpd.slpd_seq_cdg)
LEFT JOIN slma ON slpd.slma_seq_cdg = slma.slma_seq_cdg
LEFT JOIN odpa ON odpd.odpd_nmr_id = odpa.odpd_nmr_id AND odpa.odpa_dato_nombre = 'tipo_modelo'
LEFT JOIN PRGN MODOPER_TRBK ON PRGN_ID_TABLA = 'MODOPER_TRBK' AND odpa.odpa_dato_valor = MODOPER_TRBK.prgn_valor
WHERE odpd.idcl_cdg_iden = '966893106'
AND MODOPER_TRBK.prgn_valor IN (5, 11)
AND (odpd.odpd_fch_real_despacho BETWEEN
TO_DATE {{dateRange1.value.start}} AND **
** TO_DATE {{dateRange1.value.end}}
ORDER BY 4, 8
;
but I get the error "ORA-00905: missing keyword",

image
image526×578 44.8 KB

JavaScript

2

Hi @Felipe_Eduardo_Gajardo_Garcia, I think you need brackets around TO_DATE. Like

TO_DATE({{dateRange1.value.start}})

or

TO_DATE("{{ dateRange1.value.start }}")

LMK if that works for you!

1 Like
3

for the same query, select the ranges but I do not take the values, I think the possibility is that in my database to query the format of the date value is "DD-MM-YYYYYY" (which would be 01-02-2024) and the value to display in the query comes out as YYYYY-MM-DD. how could I solve the format of the value that enters the query?

select sysdate FCH_CONSULTA,
rcpn.bdin_cdg BODEGA,
srar.artl_cdg SKU,
(SELECT LISTAGG(prgn_obs, ', ') WITHIN GROUP (ORDER BY prgn_obs) "FAMILIA"
FROM prgn
where prgn_id_tabla='MSXF_'||'966893106' and prgn_valor=srar.artl_cdg) FAMILIA
,srar.srar_cdg SERIE
,decode(srar.odpd_nmr_id, null, 'En Bodega', 'Despachada') Estado
,decode(srar.odpd_nmr_id, null, coalesce(rcpn.rcpn_fch_cierre_recepcion, rcpn.rcpn_fch_ing),
odpd.odpd_fch_real_despacho) Fecha_UM --ultimo MOV
,rcpn.rcpn_fch_ing Fecha_ING
,rcpn.rcpn_fch_cierre_recepcion fch_cierre_rcpn
,odpd.odpd_fch_real_despacho fch_real_despacho_Mcnzdo
,DTPD.DTPD_CNT_DESPACHADA CANTIDAD_DESPACHADA /**/
,decode(odpd.TPOP_CDG, 'TF', 'TRANSFERENCIA', 'DE', 'DESPACHO', odpd.TPOP_CDG) TIPO_OPERACION
,MODOPER_TRBK.PRGN_OBS TIPO_PEDIDO
,odpd.odpd_nmr_folio folio_OP
,rcpn.tpee_cdg RCPN_ESTADO
,odpd.tpee_cdg OP_ESTADO
,odpd.odpd_orden_servicio OS
,slpd.slpd_numero
,slpd.
from srar
inner join rcpn on srar.rcpn_cdg=rcpn.rcpn_cdg
inner join odpd on srar.odpd_nmr_id=odpd.odpd_nmr_id
inner join dtpd on srar.odpd_nmr_id=dtpd.odpd_nmr_id
and dtpd.dtpd_eliminada=0
and srar.artl_cdg=dtpd.artl_cdg and srar.dtpd_linea=dtpd.dtpd_linea
left join slpd on odpd.odpd_nmr_folio=to_char(slpd.slpd_seq_cdg)
left join slma on slpd.slma_seq_cdg=slma.slma_seq_cdg
left join odpa on odpd.odpd_nmr_id=odpa.odpd_nmr_id and odpa.odpa_dato_nombre='tipo_modelo'
left join PRGN MODOPER_TRBK on PRGN_ID_TABLA='MODOPER_TRBK' and odpa.odpa_dato_valor=MODOPER_TRBK.prgn_valor
WHERE
odpd.idcl_cdg_iden='966893106' AND MODOPER_TRBK.prgn_valor IN (5, 11) AND
odpd.odpd_fch_real_despacho BETWEEN
TO_DATE('{{dateRange1.value.start}}', 'DD-MM-YYYY') AND
TO_DATE('{{dateRange1.value.end}}', 'DD-MM-YYYY')
)

image