Problem with a query in Postgres that I want to move to SQL Server

Question

I have the following query in postgres that I now need to run on SQL Server. Obviously I have already changed the trunc per round and basic things, but mainly I have a problem in the principle select distinct on (c.cod_socio) tbl. * Since SQL Server does not recognize that syntax.

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

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Sep 27 '18 at 04:33

Paul Maxwell · Accepted Answer · 2018-09-26T23:05:58.913

distinct on (...) simply retains the "first row" which may be emulated using row_number() over(...) and a following where clause predicate that limits to one row per partition.. Note that distinct on relies on the order by clause to decide the "first row", so you need the equivalent conditions in the over clause. Also note if you after greater query compatibility between the two databases you could use the same row_number approach in PostgreSQL.

SELECT
    *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY c.cod_socio ORDER BY h.fec_movimiento DESC) AS rn
      , 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'
) AS d
WHERE d.rn = 1
ORDER BY
    d.cod_socio
  , d.fec_movimiento DESC

Just as an aside, I really suggest you **do not use** `DATE(..)` in the where clause for your date range please see https://stackoverflow.com/a/25564544/2067753 and you probably should not use `between` for your date range either , see https://stackoverflow.com/a/26190806/2067753 — Paul Maxwell, Sep 26 '18 at 23:08

Problem with a query in Postgres that I want to move to SQL Server

1 Answers1