last change of table record using MAX function

Question

last change of table record using MAX function

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm trying to get all the records of a table taking the last change so I used the MAX () function but it is not returning any results.

These are my conditions:

SELECT DISTINCT 
       U.USS_CODIGO,
       T.CON_CODIGO, 
       t.CON_SEQUENCIA,
       t.PLA_NUMERO, 
       T.PPC_DT_ALT, 
       t.PDT_CODIGO,
       t.PPC_DT_VIGENCIA,
       T.PPC_VAL_MENSALIDADE, 
       t.PPC_IDADE_MIN 
  FROM preco_produto_contrato t, 
       CONTRATANTE C, 
       USUARIO U 
 WHERE T.PPC_DT_ALT = (SELECT MAX(T.PPC_DT_ALT)
                         FROM preco_produto_contrato t 
                        WHERE T.con_codigo = c.con_codigo 
                          AND t.PPC_DT_EXC IS NULL 
                          AND U.CON_CODIGO = C.CON_CODIGO 
                          AND U.CON_CODIGO = T.CON_CODIGO 
                          AND c.con_dt_exc IS NULL 
                          AND t.pla_numero in (105) 
                          AND t.con_sequencia = c.con_sequencia)

oracle11g

asked by anonymous 30.05.2016 / 15:10

2 answers

List AD groups through a query innerHTML only takes effect once, and dynamically generated elements

score 1 · Answer 1

Alan,

I think the query you want looks something like this:

SELECT DISTINCT 
    U.USS_CODIGO,
    T.CON_CODIGO, 
    T.CON_SEQUENCIA,
    T.PLA_NUMERO, 
    T.PPC_DT_ALT, 
    T.PDT_CODIGO,
    T.PPC_DT_VIGENCIA,
    T.PPC_VAL_MENSALIDADE, 
    T.PPC_IDADE_MIN 
FROM 
    PRECO_PRODUTO_CONTRATO T
    JOIN CONTRATANTE C ON T.CON_SEQUENCIA = C.CON_SEQUENCIA
    JOIN USUARIO U ON U.CON_CODIGO = C.CON_CODIGO AND U.CON_CODIGO = T.CON_CODIGO
WHERE 
    T.PPC_DT_ALT = (SELECT MAX(T2.PPC_DT_ALT) FROM PRECO_PRODUTO_CONTRATO T2 WHERE T2.CON_CODIGO = T.CON_CODIGO)
    AND T.PPC_DT_EXC IS NULL
    AND C.CON_DT_EXC IS NULL 
    AND T.PLA_NUMERO IN (105);

As user Motta commented, do not use the same alias as your main query in your subquery.

In addition, to make the query more readable, use JOINs to separate join clauses from filter clauses.

score 0 · Answer 2

But if you run the subquerie:

(SELECT MAX(T.PPC_DT_ALT)
FROM preco_produto_contrato t 
WHERE T.con_codigo = c.con_codigo 
AND t.PPC_DT_EXC IS NULL 
AND U.CON_CODIGO = C.CON_CODIGO 
AND U.CON_CODIGO = T.CON_CODIGO 
AND c.con_dt_exc IS NULL 
AND t.pla_numero in (105) 
AND t.con_sequencia = c.con_sequencia)

Give something?