CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-insert
Forget
Forget

Reputation: 152

Select inside an insert into.. values(...)

I have this piece of sql code. It is a trigger that activates when STATE in ORDER_SERVICE is changed INSERTING into a table called sms the sms_number (it's a sequence) and the CODIGO_CLIENTE. I have this:

create or replace TRIGGER ENVIAR_SMS 
AFTER UPDATE ON ORDEM_SERVICO
FOR EACH ROW
BEGIN
  IF UPDATING 
  THEN
     IF ( NOT :NEW.ESTADO = :OLD.ESTADO) 
     THEN
        INSERT INTO SMS(N_SMS, CODIGO_CLIENTE) 
            VALUES (SEQ_SMS.NEXTVAL, SELECT CODIGO_CLIENTE 
                                       FROM ORDEM_SERVICO, 
                                            VEICULO 
                                      WHERE ORDEM_SERVICO.MATRICULA=VEICULO.MATRICULA);
     END IF;
  END IF;
END;

It's basically a join. I have MATRICULA in ORDEM_SERVICO as a FOREIGN KEY and in VEICULO as PRIMARY KEY. I also have (and need) CODIGO_CLIENTE in VEICULO, thus the join. It complains however about the select I am doing in the VALUES. Any solution for this please?

Upvotes: 1

Views: 4917

Answers (1)

Barranka
Barranka

Reputation: 21047

Simply enclose the select with parenthesis:

INSERT INTO SMS(N_SMS, CODIGO_CLIENTE) 
    VALUES (SEQ_SMS.NEXTVAL
            , (SELECT CODIGO_CLIENTE 
               FROM ORDEM_SERVICO, VEICULO 
               WHERE ORDEM_SERVICO.MATRICULA = VEICULO.MATRICULA)
           )

Also, be sure that the select returns only one row and one column.

Upvotes: 3

Related Questions