CODEWITHSUNDEEP
db2zos
Chad
Chad

Reputation: 24679

DB2: invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value

A user can only modify the ST_ASSMT_NM and , CAN_DT columns in the ST_ASSMT_REF record. In our system, we keep history in the same table and we never really update a record, we just insert a new row to represent the updated record. As a result, the "active" record is the record with the greatest LAST_TS timestamp value for a VENDR_ID. To prevent the possibility of an update to columns that cannot be changed, I wrote the logical UPDATE so that it retrieves the non-changable values from the original record and copies them to the new one being created. For the fields that can be modified, I pass them as params,

INSERT INTO GSAS.ST_ASSMT_REF
(
        VENDR_ID
     ,ST_ASSMT_NM
     ,ST_CD
     ,EFF_DT
     ,CAN_DT
     ,LAST_TS
     ,LAST_OPER_ID
)
SELECT
        ORIG_ST_ASSMT_REF.VENDR_ID
     ,@ST_ASSMT_NM
     ,ORIG_ST_ASSMT_REF.ST_CD
     ,ORIG_ST_ASSMT_REF.EFF_DT
     ,@CAN_DT
     ,CURRENT TIMESTAMP
     ,@LAST_OPER_ID
FROM
        (
                SELECT
                         ST_ASSMT_REF_ACTIVE_V.VENDR_ID
                        ,ST_ASSMT_REF_ACTIVE_V.ST_ASSMT_NM
                        ,ST_ASSMT_REF_ACTIVE_V.ST_CD
                        ,ST_ASSMT_REF_ACTIVE_V.EFF_DT
                        ,ST_ASSMT_REF_ACTIVE_V.CAN_DT
                        ,CURRENT TIMESTAMP
                        ,ST_ASSMT_REF_ACTIVE_V.LAST_OPER_ID
                FROM
                        G2YF.ST_ASSMT_REF_ACTIVE_V ST_ASSMT_REF_ACTIVE_V --The view of only the most recent, active records
                WHERE 
                        ST_ASSMT_REF_ACTIVE_V.VENDR_ID = @VENDR_ID
        ) ORIG_ST_ASSMT_REF;

However, I am getting this error:

DB2 SP
: 
ERROR [42610] [IBM][DB2] SQL0418N The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value.

It appears as though DB2 will not allow me to use a variable in a SELECT statement. For example, when I do this in TOAD for DB2:

select 1, @vendorId from SYSIBM.SYSDUMMY1

I get a popup dialog box. When I provide any string value, I get the same error.

I usually use SQL Server and I'm pretty sure I wouldn't have an issue doing this but I am not sure how to handle it get.

Suggestions? I know that I could do this in two seperate commands, 1 query SELECT to retreive the original VALUES and then supply the returned values and the modified ones to the INSERT command, but I should be able to do thios in one. Why can't I?

Upvotes: 0

Views: 5016

Answers (1)

bhamby
bhamby

Reputation: 15450

As you mentioned in your comment, DB2 is really picky about data types, and it wants you to cast your variables into the right data types. Even if you are passing in NULLs, sometimes DB2 wants you to cast the NULL to the data type of the target column.

Here is another answer I have on the topic.

Upvotes: 1

Related Questions