0

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

3 Answers 3

1

You probably need to use keep dense_rank aggregate function like below :

SELECT
    a.GESTOR,
    a.TIPO,
    a.NUMERO,
    max(a.seq) keep (dense_rank first order by a.seq desc) as seq,
    max(a.data) keep (dense_rank first order by a.seq desc) as data,
    max(a.valor) keep (dense_rank first order by a.seq desc) as valor,
    max(a.cod_nloc) keep (dense_rank first order by a.seq desc) as cod_nloc,
    max(a.usuario) keep (dense_rank first order by a.seq desc) 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
Sign up to request clarification or add additional context in comments.

Comments

0

You cannot return multiple columns from 1 subquery. So you syntax is wrong. Alternatively you can write your query like -

SELECT
    a.GESTOR,
    a.TIPO,
    a.NUMERO,
    b.seq,
    b.data,
    b.valor,
    b.cod_nloc,
    b.usuario
FROM
    SM_HISTPATR_SE90 a
JOIN (SELECT seq, data, valor, cod_nloc, usuario
        FROM SM_HISTPATR_SE90
         AND seq = (SELECT MAX(seq)
                      FROM SM_HISTPATR_SE90
                     WHERE b.movimento = 'E')) b ON b.numero = a.numero
WHERE
    a.DATA <= '01/01/2020' and
    a.MOVIMENTO = 'E' and
    numero = 66480
GROUP BY a.GESTOR, a.TIPO, a.NUMERO

Comments

0

If you use that repeating code as a CTE (Common Table Expression) a.k.a. the WITH factoring clause, query would look like this:

with temp as
  (select b.numero, 
          max(b.seq) max_seq
   from sm_histpatr_se90 b
   where b.movimento = 'E'
   group by b.numero
  )
select a.gestor, 
       a.tipo, 
       a.numero, 
       a.data, 
       a.valor, 
       a.cod_nloc, 
       a.usuario
from sm_histpatr_se90 a join temp t on t.numero = a.numero and a.seq = t.max_seq
where a.data <= date '2020-01-01'
  and a.movimento = 'E'
  and a.numero = 66480;

Also, note that you're most probably comparing date to a string, here: a.DATA <= '01/01/2020'; you should use either a date literal (as I did) or TO_DATE function with appropriate format mask, such as a.data <= to_date('01.01.2020', 'dd.mm.yyyy').

Yet another thing: you've used table alias b twice, in different context. Try to avoid that because it makes confusion.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.