Q
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

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

This was last published in July 2003

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close