How to insert values from a subquery in merge sentence

Question

I want to insert into a merge statement of a stored procedure at ORACLE a value that comes from a subquery. How can I do that? This is what I design but it doesn't work.

CREATE OR REPLACE PROCEDURE P_FORMULARIO
IS
BEGIN 
    MERGE INTO HT_FORMULARIO d
    USING
    (SELECT id, METODO_ID, TIPO_ID, DEPARTAMENTO_ID, EQUIPAMEINTO_ID, FECHAINICIO 
        FROM ODS_FORMULARIO) o
    ON  (d.ID_CAMPO = o.id)
    WHEN MATCHED THEN
        UPDATE SET d.SK_METODO_MUEST = o.METODO_ID,
        d.SK_TIPO_MUESTRA = o.TIPO_ID,
        d.SK_DEPARTAMENTO = o.DEPARTAMENTO_ID,
        d.SK_EQUIPAMIENTO = o.EQUIPAMEINTO_ID
    WHEN NOT MATCHED THEN
        INSERT (ID_CAMPO, SK_METODO_MUEST, SK_TIPO_MUESTRA, SK_DEPARTAMENTO, SK_EQUIPAMIENTO)
        VALUES (o.id, o.METODO_ID, o.TIPO_ID,o.DEPARTAMENTO_ID, o.EQUIPAMEINTO_ID, 
            (select SK_FECHA from dt_fecha where mes = MONTH( o.FECHAINICIO) and anio = YEAR(o.FECHAINICIO));
    COMMIT;
END P_EQUIPAMIENTO;

I don't know if that is possible, thanks for the help.

Answer 1

There are some things wrong in your procedure

• The subselect below the insert does not make any sense, as you have only five fields in the insert clause and five values in the values section.
• You are getting all the records from ODS_FORMULARIO that are matching with the target table HT_FORMULARIO based on the ID, then use that part to get the values of year and month there.
• It is not clear whether you want to do with year and month. Those are java in-build funtions, not SQL.
• The insert misses a field.

Your functions don't work in SQL:

SQL> select month(sysdate) from dual ;
select month(sysdate) from dual
       *
ERROR at line 1:
ORA-00904: "MONTH": invalid identifier

SQL> select year(sysdate) from dual ;
select year(sysdate) from dual
       *
ERROR at line 1:
ORA-00904: "YEAR": invalid identifier

Keep in mind the way to get this year and month from your dt_fecha table, some options you might have for that are:

SQL> select to_char(sysdate,'Month') from dual ;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
October

SQL> select to_char(sysdate,'YYYY') from dual ;

TO_C
----
2021

SQL> select extract(month from sysdate) from dual ;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
                       10

SQL>  select extract(year from sysdate) from dual ;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2021

Then you have the column MES in the table DT_FECHA. If you have the month store with the name, you need to use NLS_LANGUAGE to get the right name according to your language. Example, in German

SQL> select TO_CHAR(SYSDATE,'fmDay, DD Month YYYY', 'NLS_DATE_LANGUAGE=''GERMAN''') from dual;

TO_CHAR(SYSDATE,'FMDAY,DDMONTHYYYY','NLS_DATE_LANGUAGE=''GERMAN''')
--------------------------------------------------------------------------------
Samstag, 16 Oktober 2021

Having say that, you want to refactor the merge and use a join in the source part of the statement to get there the sk_fecha value.

Important: Remember that I am assuming some things, like the month is stored in Spanish but not in uppercase. If it is stored in uppercase, use the function upper before to_char. If it is in another language, use the proper nls_language. If the month is stored as a number, then you can use to_char with 'MM' or extract as in my example above

CREATE OR REPLACE PROCEDURE P_FORMULARIO
IS
begin 
    MERGE INTO HT_FORMULARIO d
    USING
    (SELECT 
       odsf.id, 
       odsf.METODO_ID, 
       odsf.TIPO_ID, 
       odsf.DEPARTAMENTO_ID, 
       odsf.EQUIPAMEINTO_ID, 
       odsf.FECHAINICIO, 
       to_char(odsf.FECHAINICIO,'Month', 'NLS_DATE_LANGUAGE=''SPANISH''') as Month, 
       to_char(odsf.FECHAINICIO,'YYYY') as Year , 
       dtf.sk_fecha 
        FROM ODS_FORMULARIO odsf 
                    join dt_fecha dft on dtf.mes = odsf.mes and dtf.anio = odsf.year ) o
    ON  (d.ID_CAMPO = o.id)
    WHEN MATCHED THEN
        UPDATE SET d.SK_METODO_MUEST = o.METODO_ID,
        d.SK_TIPO_MUESTRA = o.TIPO_ID,
        d.SK_DEPARTAMENTO = o.DEPARTAMENTO_ID,
        d.SK_EQUIPAMIENTO = o.EQUIPAMEINTO_ID
    WHEN NOT MATCHED THEN
        INSERT (ID_CAMPO, SK_METODO_MUEST, SK_TIPO_MUESTRA, SK_DEPARTAMENTO, SK_EQUIPAMIENTO , --a field here is missing--)
        VALUES (o.id, o.METODO_ID, o.TIPO_ID,o.DEPARTAMENTO_ID, o.EQUIPAMEINTO_ID, o.SK_FECHA );
    COMMIT;
END P_EQUIPAMIENTO;