[postgresql] Query

Viewer

*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.

copy download embed printName: Query

WITH filter (varinicio, varfin, varfoliocomercial, varmotivo, varprioridad, vartiposolicitud, varproducto, varcliente, varfechainicio, varfechafin, varhavelistfolio, varactivos, varestatusrecuperacion, varubicacion, vardireccion) AS ( values(1, 10, '', '', '', '', '', '', '', '', 'false', '', '', '', ''))
SELECT recuperacion_id,
orden_servicio_productos_id,
prioridad_id,
prioridad,
motivo_id,
motivo,
tiposolicitud_id,
tiposolicitud,
producto,
os_id,
os_folio_comercial,
cliente_id,
cliente_nombre,
to_char(recuperacion_fecha_registro, 'DD/MM/YYYY') AS recuperacion_fecha_registro,
recuperacion_activa,
estatus_recuperacion_id,
estatus_recuperacion,
ubicacion_id,
ubicacion,
ubicacion_codigo,
direccion,
asignado_a,
to_char(ultima_actualizacion, 'DD/MM/YYYY HH24:MI') AS ultima_actualizacion
FROM (
SELECT *
FROM (
SELECT *,
row_number() OVER ( ORDER BY recuperacion_id ASC ) AS row
FROM (
SELECT req.id_recuperacion AS recuperacion_id,
NULL AS orden_servicio_productos_id,
ccp.id AS prioridad_id,
ccp.prioridad AS prioridad,
ccm.id AS motivo_id,
ccm.motivo AS motivo,
cct.id AS tiposolicitud_id,
cct.tiposolicitud AS tiposolicitud,
cir.nombre_circuito AS producto,
os.id_orden_servicio AS os_id,
os.folio_os_comercial AS os_folio_comercial,
cli.id_cliente AS cliente_id,
cli.nombre AS cliente_nombre,
req.fecha_inicio AS recuperacion_fecha_registro,
req.activo AS recuperacion_activa,
req.id_estatus AS estatus_recuperacion_id,
cere.descripcion AS estatus_recuperacion,
ub.id_ubicacion AS ubicacion_id,
ub.ubicacion AS ubicacion,
ub.cod_preventa AS ubicacion_codigo,
dir.calle
|| ', '
|| dir.no_int
|| ', '
|| dir.no_ext
|| ', '
|| dir.colonia
|| ', '
|| cd.nombre_ciudad
|| ', '
|| dir.codigo_postal AS direccion,
req.asignado_a AS asignado_a,
(
SELECT max(fecha_registro)
FROM "SEGUIMIENTO_RECUPERACION"."RECUPERACION_EQUIPOS_USUARIOS_ATENCION"
WHERE id_recuperacion = req.id_recuperacion) AS ultima_actualizacion
FROM "SEGUIMIENTO_RECUPERACION"."SOLICITUD_RECUPERACION" AS sr
INNER JOIN "DATOS_SISTEMAS"."CAT_TIPO_PRODUCTOS" AS ctp
ON sr.id_cat_tipo_producto = ctp.id_cat_tipo_producto
INNER JOIN "ENI"."CANCELACIONES" AS can
ON can.idcancelacion = sr.id_cancelacion
INNER JOIN "ENI"."CIRCUITO" AS cir
ON cir.id_circuito = can.id_circuito
LEFT JOIN "ENI"."DIRECCION" AS dir
ON dir.id_circuito = cir.id_circuito
LEFT JOIN "ENI"."CIUDAD" AS cd
ON cd.idciudad = dir.idciudad
LEFT JOIN "ENI"."ESTADO" AS edo
ON edo.idestado = cd.idestado
INNER JOIN "ENI"."ORDEN_SERVICIO" AS os
ON os.id_orden_servicio = cir.id_orden_servicio
INNER JOIN "ENI"."CLIENTE" AS cli
ON cli.id_cliente = os.id_cliente
INNER JOIN "ENI"."CANC_CAT_PRIORIDAD" AS ccp
ON can.prioridad = ccp.id
INNER JOIN "ENI"."CANC_CAT_MOTIVO" AS ccm
ON can.motivo = ccm.id
INNER JOIN "ENI"."CANC_CAT_TIPOSOLIC" AS cct
ON can.tiposolicitud = cct.id
INNER JOIN "DATOS_SISTEMAS"."FOLIOS_PREVENTA" AS fp
ON fp.folio_preventa = os.folio_os_comercial
INNER JOIN "DATOS_SISTEMAS"."CAT_UBICACION" AS ub
ON ub.id_ubicacion = fp.id_ubicacion
INNER JOIN "SEGUIMIENTO_RECUPERACION"."RECUPERACION_EQUIPOS" AS req
ON req.id_recuperacion = sr.id_recuperacion
INNER JOIN "SEGUIMIENTO_RECUPERACION"."CAT_ESTATUS_RECUPERACION_EQUIPO" AS cere
ON cere.id_estatus = req.id_estatus
UNION ALL
SELECT req.id_recuperacion AS recuperacion_id,
osp.id_orden_servicio_productos AS orden_servicio_productos_id,
NULL AS prioridad_id,
NULL AS prioridad,
NULL AS motivo_id,
NULL AS motivo,
NULL AS tiposolicitud_id,
NULL AS tiposolicitud,
cp.descripcion AS producto,
os.id_orden_servicio AS os_id,
os.folio_os_comercial AS os_folio_comercial,
cli.id_cliente AS cliente_id,
cli.nombre AS cliente_nombre,
req.fecha_inicio AS recuperacion_fecha_registro,
req.activo AS recuperacion_activa,
req.id_estatus AS estatus_recuperacion_id,
cere.descripcion AS estatus_recuperacion,
ub.id_ubicacion AS ubicacion_id,
ub.ubicacion AS ubicacion,
ub.cod_preventa AS ubicacion_codigo,
dir.calle
|| ', '
|| dir.no_int
|| ', '
|| dir.no_ext
|| ', '
|| dir.colonia
|| ', '
|| cd.nombre_ciudad
|| ', '
|| dir.codigo_postal AS direccion,
req.asignado_a AS asignado_a,
(
SELECT max(fecha_registro)
FROM "SEGUIMIENTO_RECUPERACION"."RECUPERACION_EQUIPOS_USUARIOS_ATENCION"
WHERE id_recuperacion = req.id_recuperacion) AS ultima_actualizacion
FROM "SEGUIMIENTO_RECUPERACION"."SOLICITUD_RECUPERACION" AS sr
INNER JOIN "SEGUIMIENTO_RECUPERACION"."RECUPERACION_EQUIPOS" AS req
ON req.id_recuperacion = sr.id_recuperacion
INNER JOIN "ENI"."ORDEN_SERVICIO_PRODUCTOS" AS osp
ON osp.id_orden_servicio_productos = sr.id_orden_servicio_productos
INNER JOIN "ENI"."ORDEN_SERVICIO" AS os
ON os.id_orden_servicio = osp.id_orden_servicio
INNER JOIN "ENI"."CIRCUITO" AS cir
ON cir.id_orden_servicio = os.id_orden_servicio
LEFT JOIN "ENI"."DIRECCION" AS dir
ON dir.id_circuito = cir.id_circuito
LEFT JOIN "ENI"."CIUDAD" AS cd
ON cd.idciudad = dir.idciudad
LEFT JOIN "ENI"."ESTADO" AS edo
ON edo.idestado = cd.idestado
INNER JOIN "DATOS_SISTEMAS"."CAT_PRODUCTOS" AS cp
ON osp.id_producto = cp.id_producto
INNER JOIN "ENI"."CLIENTE" AS cli
ON cli.id_cliente = os.id_cliente
INNER JOIN "DATOS_SISTEMAS"."FOLIOS_PREVENTA" AS fp
ON fp.folio_preventa = os.folio_os_comercial
INNER JOIN "DATOS_SISTEMAS"."CAT_UBICACION" AS ub
ON ub.id_ubicacion = fp.id_ubicacion
INNER JOIN "SEGUIMIENTO_RECUPERACION"."CAT_ESTATUS_RECUPERACION_EQUIPO" AS cere
ON cere.id_estatus = req.id_estatus
WHERE req.id_recuperacion NOT IN
(
SELECT id_recuperacion
FROM "SEGUIMIENTO_RECUPERACION"."SOLICITUD_RECUPERACION"
WHERE id_orden_servicio_productos IS NULL) ) AS tabla
WHERE
CASE
WHEN
(
SELECT varactivos ::text
FROM filter) = '' THEN true = true
ELSE recuperacion_activa =
(
SELECT varactivos ::bool
FROM filter)
END
AND
CASE
WHEN
(
SELECT varfoliocomercial ::text
FROM filter) = '' THEN true = true
ELSE os_folio_comercial =
(
SELECT varfoliocomercial ::text
FROM filter)
END
AND
CASE
WHEN
(
SELECT varprioridad ::text
FROM filter) = '' THEN true = true
ELSE prioridad_id =
(
SELECT varprioridad ::integer
FROM filter)
END
AND
CASE
WHEN
(
SELECT varmotivo ::text
FROM filter) = '' THEN true = true
ELSE motivo_id =
(
SELECT varmotivo ::integer
FROM filter)
END
AND
CASE
WHEN
(
SELECT vartiposolicitud ::text
FROM filter) = '' THEN true = true
ELSE tiposolicitud_id =
(
SELECT vartiposolicitud ::integer
FROM filter)
END
AND
CASE
WHEN
(
SELECT varestatusrecuperacion ::text
FROM filter) = '' THEN true = true
ELSE estatus_recuperacion_id =
(
SELECT varestatusrecuperacion ::integer
FROM filter)
END
AND
CASE
WHEN
(
SELECT varubicacion ::text
FROM filter) = '' THEN true = true
ELSE ubicacion_codigo =
(
SELECT varubicacion ::text
FROM filter)
END
AND
CASE
WHEN
(
SELECT varfechainicio::text
FROM filter) = '' THEN true = true
ELSE
CASE
WHEN
(
SELECT varfechafin::text
FROM filter) = '' THEN recuperacion_fecha_registro BETWEEN to_timestamp(((to_date(
(
SELECT varfechainicio ::text
FROM filter), 'YYYY-MM-DD'))::text)
|| ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_timestamp(((to_date(
(
SELECT varfechainicio ::text
FROM filter), 'YYYY-MM-DD'))::text)
|| ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
ELSE recuperacion_fecha_registro BETWEEN to_timestamp(((to_date(
(
SELECT varfechainicio ::text
FROM filter), 'YYYY-MM-DD'))::text)
|| ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND to_timestamp(((to_date(
(
SELECT varfechafin ::text
FROM filter), 'YYYY-MM-DD'))::text)
|| ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
END
END
AND
CASE
WHEN
(
SELECT varhavelistfolio::bool
FROM filter)= false THEN true = true
ELSE os_folio_comercial IN ('')
END
AND upper(producto) LIKE concat('%', upper(
(
SELECT varproducto ::text
FROM filter)), '%')
AND upper(cliente_nombre) LIKE concat('%', upper(
(
SELECT varcliente ::text
FROM filter)), '%')
AND upper(
CASE
WHEN direccion IS NULL THEN ''
ELSE direccion
END) LIKE concat('%', upper(
(
SELECT vardireccion ::text
FROM filter)), '%') ) AS tabla
ORDER BY recuperacion_id ASC ) AS tabla
WHERE row BETWEEN
(
SELECT varinicio::integer
FROM filter)
AND
(
SELECT varfin::integer
FROM filter)

Editor

You can edit this paste and save as new:

fullscreen copy clear

Syntax Highlighting

Title / Paste Name

Meta robots tag

reCaptcha

File Description

Query
Paste Code
17 Sep-2021
42.98 Kb

You can Share it:

Latest Code Pastes

Full list

Tools

[postgresql] Query

Viewer

Editor