last table record change using MAX function

Asked

Viewed 62 times

1

I’m trying to bring all the records of a table by taking the last change so I used the MAX() function but it’s not returning any results.

These are my terms:

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
  • 1

    Friend, add more information such as table structure, data example, expected result... And the complete select.

  • 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)

  • Friend, when it is necessary to add more information in your question you can edit it. In this case the SELECT informed in your comment should be added in your question. I have already provided the edit. Once a moderator approves the SELECT will appear in the body of the question.

  • 1

    The alias of the preco_product_contact table cannot be the same , in the case "t" for the two instances of the table , select main and sub , exchange the of the sub for something like "t2" .

  • uses a order by desc.

2 answers

1

Alan,

I believe the query you want is 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 Motta commented, do not use the same alias as your main query in your subquery.

Also, to make the query more readable, use Joins to separate join clauses from filter clauses.

0

But if you execute 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)

Gives something?

  • 1

    Welcome to Stack Overflow! It’s nice that you want to help, but you used an answer to comment. You can post comments when you have a little more reputation points. Please use the answer field only to post a solution to the problem the question deals with.

Browser other questions tagged oracle11g

You are not signed in. Login or sign up in order to post.