mercredi 25 juillet 2018

if-statement in mybatis insert query

I would like to know how can I put if-statement inside a insert SQL-query. I would like to insert two parameters (CODRIC and PROGRIC) only if they are not null. I write something like this

INSERT
        INTO
            ${schema}.I9TB1000
            (
                I9TB1000_PROGR,
                I9TB1000_DATARIF,
                I9TB1000_STATO,
                I9TB1000_STATOP,
                I9TB1000_FUNZ,
                I9TB1000_CODRIC,
                I9TB1000_PROGRIC,
                I9TB1000_INSMATR,
                I9TB1000_INSTERM
            )
            VALUES( 
                NEXT VALUE FOR ${schema}.I9TB001S,
                (SELECT
                    I9TB1000_DATARIF
                FROM
                    ${schema}.I9TB1000
                WHERE
                    I9TB1000_PROGR =
                    (
                        SELECT
                            MAX (I9TB1000_PROGR)
                        FROM
                            ${schema}.I9TB1000)),
                (   SELECT
                        COALESCE(I9TBFUNZ_STA_TO,#{i9TB1000_STATOP, jdbcType=INTEGER})
                    FROM
                        ${defaultSchema}.I9TBFUNZ
                    WHERE
                        I9TBFUNZ_FUNZ = #{i9TB1000_FUNZ, jdbcType=INTEGER} ), 
                #{i9TB1000_STATOP, jdbcType=INTEGER},
                #{i9TB1000_FUNZ, jdbcType=INTEGER},
                <if test="i9TB1000_CODRIC != null">
                    #{i9TB1000_CODRIC, jdbcType=CHAR},
                </if>
                <if test="i9TB1000_PROGRIC != null">
                    #{i9TB1000_PROGRIC, jdbcType=INTEGER},
                </if>
                #{insMatr, jdbcType=CHAR},
                #{insTerm, jdbcType=CHAR}
            )

I take error -117 THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED COLUMNS. It seems that mybatis couldn't read the if-statements... Someone could explain me why?
Thank you in advance

Aucun commentaire:

Enregistrer un commentaire