OraFAQ Forum: SQL & PL/SQL » Subtrair valores

Home » SQL & PL/SQL » SQL & PL/SQL » Subtrair valores (Oracle SQL Developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Subtrair valores [message #687591]

Mon, 10 April 2023 09:15

FlaSouza
Messages: 1
Registered: April 2023

Junior Member

Pessoal podem me ajudar
Eu quero subtrair dois valores direto na inserção, como faço ... exemplo do meu código

INSERT INTO TABELA_CLIENTES_SHINA ( NOME, ENDERECO, BAIRRO, CIDADE, ESTADO, TELEFONE, DATA_DE_COMPRA, VALOR_DA_VENDA, DESCONTOS,
VALOR_LIQUIDO, ITENS, FORMA_DE_PAGAMENTO)
VALUES
('José' , 'Av. Brasil 200' , 'Brasil' 'Uberlândia', 'MG', '34998821707', TO_DATE ('2023-01-26', 'DD-MM-YYYY'), 111.00,
1.52, ('valor_da_venda - desconto')
Nesse ponto eu quero subtrair o valor da venda, com os descontos para já calcular automaticamente o valor_liquido

Como posso fazer alguém pode me auxiliar?

Report message to a moderator

Re: Subtrair valores [message #687592 is a reply to message #687591]

Mon, 10 April 2023 10:07

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Generally speaking it is a bad idea to store computed value from other columns in the table.
The main reason is that you can have inconsistencies between these values.

The best solution is to have a virtual column:

SQL> create table t (
  2    val1 integer,
  3    val2 integer,
  4    tot  integer generated always as (val1+val2))
  5  /

Table created.

SQL> insert into t (val1, val2) values (10,20);

1 row created.

SQL> select * from t;
      VAL1       VAL2        TOT
---------- ---------- ----------
        10         20         30

1 row selected.

Report message to a moderator

Re: Subtrair valores [message #687593 is a reply to message #687591]

Mon, 10 April 2023 11:23

Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA

Senior Member

C##SCOTT@XE_21.3.0.0.0> CREATE TABLE TABELA_CLIENTES_SHINA
  2    (NOME		 VARCHAR2(10),
  3  	ENDERECO	 VARCHAR2(15),
  4  	BAIRRO		 VARCHAR2(10),
  5  	CIDADE		 VARCHAR2(15),
  6  	ESTADO		 VARCHAR2(2),
  7  	TELEFONE	 VARCHAR2(12),
  8  	DATA_DE_COMPRA	 DATE,
  9  	VALOR_DA_VENDA	 NUMBER,
 10  	DESCONTOS	 NUMBER,
 11  	VALOR_LIQUIDO	 NUMBER GENERATED ALWAYS AS (VALOR_DA_VENDA - DESCONTOS) VIRTUAL)
 12  /

Table created.

C##SCOTT@XE_21.3.0.0.0> INSERT INTO TABELA_CLIENTES_SHINA
  2    (NOME, ENDERECO, BAIRRO, CIDADE, ESTADO, TELEFONE, DATA_DE_COMPRA, VALOR_DA_VENDA, DESCONTOS)
  3  VALUES
  4    ('José' , 'Av. Brasil 200' , 'Brasil', 'Uberlândia', 'MG', '34998821707',
  5  	-- FORMATO DE DATA CORRIGIDO
  6  	TO_DATE ('2023-01-26', 'YYYY-MM-DD'),
  7  	111.00, 1.520)
  8  /

1 row created.

C##SCOTT@XE_21.3.0.0.0> COMMIT
  2  /

Commit complete.

C##SCOTT@XE_21.3.0.0.0> SELECT * FROM TABELA_CLIENTES_SHINA
  2  /

NOME       ENDERECO        BAIRRO     CIDADE          ES TELEFONE     DATA_DE_C 
---------- --------------- ---------- --------------- -- ------------ --------- 
VALOR_DA_VENDA  DESCONTOS VALOR_LIQUIDO                                         
-------------- ---------- -------------                                         
José      Av. Brasil 200  Brasil     Uberlândia     MG 34998821707  26-JAN-23 
           111       1.52        109.48                                         
                                                                                

1 row selected.

Report message to a moderator

Previous Topic:	Oracle Style with Table Alias
Next Topic:	sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 25 02:32:48 CDT 2023