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
from TEST_ADM
where appl_nr = :appl_nr
end-exec
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
:ws-code-kind-ind
from TEST_ADM
where appl_nr = :appl_nr
end-exec
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."
Cheers
