Problem solve Get help with specific problems with your technologies, process and projects.

Can you help me with this query?

Hi Craig,

I have gone through your DB2 complete reference and other articles published by you ... believe it or not .. it is so informative and has answered almost all of my queries and doubts .. nothing prevents me from referring it as the best of its kind ... I need your expertise on a query ... am having a slight problem with this query .. can you help me out with this?

This is the embedded SQL being used

Table:- TEST_ADM

column name :- code_kind , X(03) NOT NULL

appl_nr , X(15) WITH DUPLICATES

exec sql

select max(code_kind) into :ws-code-kind


where appl_nr = :appl_nr


And while trying to execute this query I am getting - 305 ......

But this query works fine if i declare an indicator variable eg:- :ws-code-kind-ind pic S9(04) usage comp

exec sql

select max(code_kind) into :ws-code-kind



where appl_nr = :appl_nr


But the question is , since this being a non nullable column why is -305 popping out ?

Any idea Craig ?

Thanks and regards

Happy to help out here. You need the indicator variable in this instance because it is possible that DB2 will return a NULL as the result of your query. Even though code_kind is defined as NOT NULL, the query is not retrieving code_kind, but MAX(code_kind).

Consider what will happed if the host variable (:appl_nr) is set to a value that does not exist in the table for that column (appl_nr). For example, if the host variable is set to 'XXXXXXXXXXXXXXX' but that value does not appear in the TEST_ADM table. In that case, DB2 will return NULL as the maximum. If you look up the description of the MAX function in the SQL REFERENCE manual you will see that it says "If the function is applied to an empty set, the result is a null value. Otherwise, the result is the maximum value in the set."

Dig Deeper on IBM DB2 management