Good morning friends, I have the following query in postgres.
select
distinct on (c.cod_socio) tbl.*, h.cod_oficina, h.cod_transaccion, h.num_transaccion, h.num_sec, h.fec_movimiento
from sgf_det_mov_his h
inner join sgf_cuenta c on c.cod_producto = h.cod_producto and c.cod_cuenta = h.cod_cuenta
inner join sgf_tran t on t.cod_transaccion = h.cod_transaccion and t.cod_oficina = h.cod_oficina and t.cod_tipo_transaccion in ('DA', 'DP','NC')
inner join
(
select sgf_cuenta.cod_socio, sum(trunc(sgf_det_mov_his.val_efectivo,0) + trunc(sgf_det_mov_his.val_cheques,0)) as total
from sgf_det_mov_his, sgf_cuenta, sgf_tran
where
sgf_cuenta.cod_producto = sgf_det_mov_his.cod_producto
and sgf_cuenta.cod_cuenta = sgf_det_mov_his.cod_cuenta
and sgf_det_mov_his.sts_mov = 'A'
and sgf_tran.cod_transaccion = sgf_det_mov_his.cod_transaccion
and sgf_tran.cod_oficina = sgf_det_mov_his.cod_oficina
and sgf_cuenta.cod_producto <> 2
and sgf_tran.cod_tipo_transaccion in ('DA', 'DP','NC')
and isnull(sgf_tran.cod_uaf,0) > 0 and isnull(sgf_tran.cod_uaf,0) not in (71)
and sgf_cuenta.cod_cuenta not in (select cod_cuenta from sgf_credito
where sgf_credito.cod_producto = sgf_cuenta.cod_producto
and sgf_credito.cod_cuenta = sgf_cuenta.cod_cuenta
and sts_operacion in ('A'))
and date(sgf_det_mov_his.fec_movimiento) between '2015-01-01' and '2019-01-01'
group by sgf_cuenta.cod_socio
having sum(trunc(sgf_det_mov_his.val_efectivo,0) + trunc(sgf_det_mov_his.val_cheques,0))>=5000
) tbl on tbl.cod_socio = c.cod_socio
where
date(h.fec_movimiento) between '2015-01-01' and '2019-01-01'
order by c.cod_socio, h.fec_movimiento desc
Now this query I need to execute in SQL-SERVER. I have made only the following changes.
select
distinct on (c.cod_socio) tbl.*, h.cod_oficina, h.cod_transaccion, h.num_transaccion, h.num_sec, h.fec_movimiento
from sgf_det_mov_his h
inner join sgf_cuenta c on c.cod_producto = h.cod_producto and c.cod_cuenta = h.cod_cuenta
inner join sgf_tran t on t.cod_transaccion = h.cod_transaccion and t.cod_oficina = h.cod_oficina and t.cod_tipo_transaccion in ('DA', 'DP','NC')
inner join
(
select sgf_cuenta.cod_socio, sum(round(sgf_det_mov_his.val_efectivo,0) + round(sgf_det_mov_his.val_cheques,0)) as total
from sgf_det_mov_his, sgf_cuenta, sgf_tran
where
sgf_cuenta.cod_producto = sgf_det_mov_his.cod_producto
and sgf_cuenta.cod_cuenta = sgf_det_mov_his.cod_cuenta
and sgf_det_mov_his.sts_mov = 'A'
and sgf_tran.cod_transaccion = sgf_det_mov_his.cod_transaccion
and sgf_tran.cod_oficina = sgf_det_mov_his.cod_oficina
and sgf_cuenta.cod_producto <> 2
and sgf_tran.cod_tipo_transaccion in ('DA', 'DP','NC')
and isnull(sgf_tran.cod_uaf,0) > 0 and isnull(sgf_tran.cod_uaf,0) not in (71)
and sgf_cuenta.cod_cuenta not in (select cod_cuenta from sgf_credito
where sgf_credito.cod_producto = sgf_cuenta.cod_producto
and sgf_credito.cod_cuenta = sgf_cuenta.cod_cuenta
and sts_operacion in ('A'))
and convert(date, sgf_det_mov_his.fec_movimiento) between '2015-01-01' and '2019-01-01'
group by sgf_cuenta.cod_socio
having sum(round(sgf_det_mov_his.val_efectivo,0) + round(sgf_det_mov_his.val_cheques,0))>=5000
) tbl on tbl.cod_socio = c.cod_socio
where
date(h.fec_movimiento) between '2015-01-01' and '2019-01-01'
order by c.cod_socio, h.fec_movimiento desc
The errors that it presents me are the following ones.
Mens. 156, Nivel 15, Estado 1, Línea 2
Sintaxis incorrecta cerca de la palabra clave 'on'.
Mens. 102, Nivel 15, Estado 1, Línea 26
Sintaxis incorrecta cerca de 'tbl'.
I would greatly appreciate your help.