Implementing view

Asked

Viewed 20 times

0

Display statement of a particular account with the respective movements of a 15 days prior to the request date with the following data:

  • Agency;
  • Account number;
  • Name of the account holder;
  • Account holder identification (CPF or CNPJ);
  • Account type;
  • Account situation;
  • Type of movement;
  • Date of movement;
  • Movement value;
  • Account balance.

I implemented this view:

create view v_extrato_conta as
select c.agencia,
       c.numero_conta, 
       t.nome,
       t.CPF_CNPJ, 
       c.tipo,
       c.estado_conta,
       m.operacao,
       m.data_movimento,
       m.valor,
       c.saldo 
from conta c 
inner join correntista t 
on c.idconta = t.idcorrentista
inner join movimento m
on c.idconta = m.idmovimento;

and I created this project to call this view:

delimiter #
create procedure exibi_extrato_conta(data_solicitacao date)
begin 
    select * from v_extrato_conta where data_solicitacao < 15;
end
#

but I can’t get back any data if I withdraw the condition of data_solicitacao < 15 and pass any request date and call Procedure, it brings me the data. How could I do this query by decreasing 15 days from data_solicitacao ? I hope you can help me.

mysql sql select-sql view procedure

  • If data_solicitacao is a parameter of your precedent what is the sense of using such a parameter in your query compared to a constant? Shouldn’t you check if data_movimento is in the period of 15 days prior to data_solicitacao?

1 answer

0

If the data type of the column data_solicitacao date is necessary to cast in order to compare the values correctly.

In your query you have a data_request (date type) and are trying to make a comparison with the whole type. That’s why the problem in your query.

In case of dates you must use the operator interval of mysql, but each database system has its date comparison functions. Assuming we want the interval between today’s date and quiz days ago, we should do:

delimiter #
create procedure exibi_extrato_conta(data_solicitacao date)
BEGIN 
    SELECT * FROM v_extrato_conta WHERE data_solicitacao BETWEEN DATE_ADD(NOW(), INTERVAL - 15 DAY) AND NOW();
END
#

In my example I used the operator between, but can also be using the larger operators equal in the way you are doing.

 delimiter #
    create procedure exibi_extrato_conta(data_solicitacao date)
    BEGIN 
        SELECT * FROM v_extrato_conta WHERE data_solicitacao <= NOW() AND 
        data_solicitacao >= DATE_ADD(NOW(), INTERVAL - 15 DAY);
    END
    #

Follow the documentation to date add mysql

Browser other questions tagged mysql sql select-sql view procedure

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