0
I’m having problems to perform the Insert in a table called accounts, however I have relationship with the company table.
In the company table I have the following fields:
Id_empresa | Nome_Empresa | Inscricao_Estadual.
255 | AMG Ltda | 08522255
In the table of accounts I have the following fields:
Id_contas | Id_Empresa | Valor | Inscricao_Estadual | Ano_Mes | Ano_Mes_Conta | ICMS | TOTAL_RECOLHIDO
However, to accomplish the insert i have to check if the state registration I am entered exists in the company table, if there is I enter, if there is no returns the insertion error.
I do not know if there is the possibility to do in the select those that do not exist do not insert and at the end show those state registrations that have no link with any company in the database. How could be treated the error I’m receiving and this validation?
INSERT INTO Contas(
id_Conta,
id_empresa,
valor,
inscricao_estadual,
ano_mes,
ano_mes_Conta,
icms,
total_recolhido)
VALUES(
365545,
'25.300,38',
'08522255',
(SELECT emp.id_empresa FROM EMPRESA emp where emp.Inscricao_Estadual = '08522255'),
'199810',
'199910',
'38.011,71',
'38.011,71');
Returns the following error:
Report of errors - ORA-01722: invalid number
If anyone can help me, I’d be grateful.
in the registered field_state, you want to enter the company id, from the table of companies ? (but that doesn’t seem to be the problem) = > Invalid number must be the numeric fields that are with . and ,. Normally in banks the decimal separator is . then the number format is incorrect
– Rovann Linhalis
Rovann Linhalis, I want to enter the company id and the state entry in the Accounts table. The select I am using is precisely for this, because through the state registration I will have to enter. The foreign key that makes the relationship in the table of accounts with the company, is through the state registration. I performed the correction for the numeric fields, but I was presented with the following error message: ORA-02291: integrity restriction (FK_EMPRESA_CONTAS) violated - mother key not located
– Robson
through select I receive the id of the company that is linked to state registration. however when trying to insert this error message is shown after the corrections of the number formats you mentioned. ORA-02291: integrity restriction (FK_EMPRESA_CONTAS) breached - mother key not located
– Robson
now the problem has become this field enrolled statewide, by the logic it should have the registration number and not the company ID. Check how FK is set.
– Rovann Linhalis