|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Hi,
I have designed a Db2 program. I got a doubt in handling null indicators. i have declared the null indicator variable as s9(4) comp.
i have a NULL field. so while inserting and updating i have moved -1 to null indicator to insert it in db2 table.
My doubt is, whether i need to move 0 to null indicator if the column defined as NULL has a valid value ?. I have moved the value to the db2 field and 0 to the null indicator during insertion and updation?. please let me know what i have done is correct ?.
i have given a sample psuedo code which i have used in my program.
if sql-code = 0
move "valid-value" to db2 field
move "0" to null-indicator
else
if sql-code = 100
move -1 to null-indicator
end-if
end-if.
Moreover during SUM(db2 field), this db2 field is a NULL field, whether it is necessary to use null indicators or is it enough to use host variables alone to handle the sum.
please help me out. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
pseudo code looks fine, just move 0 and not "0"
| Code: |
| select sum(col) from tab where ... |
- returns NULL when no rows qualify the where clause, even when field itself is not nullable.
- returns null when all "col" from qualifying rows are null
so, yes you have to use null-indicator or use the function coalesce() :
| Code: |
| select coalesce(sum(col),0) from tab where ... |
|
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
if you really want to learn this and remember it, I suggest you create a few sandbox tables to play with, then test each variant of how this might work, and see the results.
Then you will be the valuable goto person in your org who knows this stuff. |
|
| Back to top |
|
|
|
|
