There's another way to return multiple fields in a sub query (Oracle SQL)? I am using this code below, but I think it has a lot of repeated code. I believe there must be some simpler way to do this:
SELECT
a.GESTOR,
a.TIPO,
a.NUMERO,
(SELECT b.seq FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero)) as seq,
(SELECT b.data FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero)) as data,
(SELECT b.valor FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero)) as valor,
(SELECT b.cod_nloc FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero)) as cod_nloc,
(SELECT b.usuario FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero)) as usuario
FROM
SM_HISTPATR_SE90 a
WHERE
a.DATA <= '01/01/2020' and
a.MOVIMENTO = 'E' and
numero = 66480
GROUP BY a.GESTOR, a.TIPO, a.NUMERO
I tried the code below but it didn't work:
SELECT
a.GESTOR,
a.TIPO,
a.NUMERO,
seq, data, valor, cod_nloc, usuario = (SELECT b.seq, b.data, b.valor, b.cod_nloc, b.usuario FROM SM_HISTPATR_SE90 b where b.seq = (SELECT max(seq) FROM SM_HISTPATR_SE90 b where b.movimento = 'E' and b.numero = a.numero))
FROM
SM_HISTPATR_SE90 a
WHERE
a.DATA <= '01/01/2020' and
a.MOVIMENTO = 'E' and
numero = 66480
GROUP BY a.GESTOR, a.TIPO, a.NUMERO